1

I have a column in a table where timestamps have been stored in VARCHAR format, but I need to compare these against a column of DATETIME values from another table to find time intervals, so I want to either cast or convert the VARCHAR timestamps to DATETIME. However, both casting and converting are giving me problems.

The format of the VARCHAR timestamp looks like this: "29/07/2012 01:53:36 +12".

Using the query:

SELECT CAST(event_timestamp AS datetime) FROM the_table

produces ERROR: date/time field value out of range: "29/07/2012 01:53:36 +12".

Using the query: 

SELECT CONVERT(datetime, event_timestamp, 131) from the_table;

produces 

ERROR:  syntax error at or near ","
LINE 1: select CONVERT(datetime, event_timestamp, 131) from the_tab...
                               ^ (note: this is pointing at the first comma).

The error with CONVERT actually happens even if you use a generic function such as getdate() for the data source. This db uses ANSI SQL-92 (or so I'm told). Could anyone please help me out with this?

Stanley Ayzenberg
  • 25
  • 1
  • 7
  • Would it be easier to convert the datetime field to a varchar for the comparison of 2 varchar fields instead? – RobJohnson Aug 08 '12 at 15:30
  • Which RDBMS? (Looks like SQL Server or Sybase - if so which version?) – Ed Harper Aug 08 '12 at 15:34
  • @RobJohnson Would I be able to find time intervals between two varchar fields? – Stanley Ayzenberg Aug 08 '12 at 15:41
  • @EdHarper Actually, it's Teradata Aster Data. – Stanley Ayzenberg Aug 08 '12 at 15:43
  • Sorry missed that, not really, what is the '+12' indicative of? The following would work for example: select CONVERT(datetime, '29/07/2012 01:53:36', 103)...although I am going by MS SQL, I'm not familiar with TAD – RobJohnson Aug 08 '12 at 15:44
  • Are you sure that the date format on the server matches the one saved on the database? If the server is set with the "m/d/y" format, the conversion won't work as "29" will be considered the month. – Diego Aug 08 '12 at 15:50
  • @StanleyAyzenberg . . . this is tagged as Teradata, but the CONVERT function is particular to SQL Server and Sybase. Is the tag correct? If so, you should be using CAST() with a FROMAT specification. – Gordon Linoff Aug 08 '12 at 16:39
  • @RobJohnson That did not work, but apparently CONVERT is not supported in the first place, which was my mistake. Also, the +12 is an adjustment from GMT as I recall. – Stanley Ayzenberg Aug 08 '12 at 17:22
  • @Diego I'm not entirely sure, I may have to look into that. – Stanley Ayzenberg Aug 08 '12 at 17:22
  • @GordonLinoff Sorry, I didn't know that CONVERT was unique to those two. This is actually Aster Data and not Teradata proper. Also, the FORMAT specification did not take, and the CAST specified in some Aster Data documentation produces the error I mentioned in the original post. – Stanley Ayzenberg Aug 08 '12 at 17:23

3 Answers3

2

This seems really painful, but the following should work:

select dateadd(hh, cast(right(tv, 3) as int),
               CONVERT(datetime, left(tv, 10), 103)+CONVERT(datetime, substring(tv, 12, 8), 108)
              )
from (select '29/07/2012 01:53:36 +12' as tv) t

I've never added datetime's before, but this just worked on SQL Server 2008.

Why can't SQL Server just support a flexible notation built around yyyy, mm, mmm, dd and so on?

The actual database is Aster Data, which is based on Postgres (as are most recent database engines). In this database, you would use to_timestamp(). See the documentation here http://www.postgresql.org/docs/8.2/static/functions-formatting.html. The call would be something like:

to_timestamp(val, 'MM/DD/YYYY HH:MI:SS tz') -- not sure if this gets the +12

There are no ANSI functions for date conversion, so each database does its own. Even string functions vary among databases (substr? substring? charindex? instr? location?), so there is no ANSI way to do this.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    +1 for "why can't SQL Server support a flexible notation". I really hate the limited functionality of the CONVERT() function. –  Aug 08 '12 at 16:33
  • This code didn't work, but that's probably because, as you mentioned above, I incorrectly assumed CONVERT was standard to SQL. – Stanley Ayzenberg Aug 08 '12 at 17:23
  • to_timestamp() worked perfectly! For anyone who may wind up in this situation, my solution is to use to_timestamp() on the VARCHAR column and CAST(columnname AS TIMESTAMP) on the DATETIME column to have them "meet in the middle." Thanks, Gordon! – Stanley Ayzenberg Aug 08 '12 at 17:53
0

You are using the wrong syntax, try:

CONVERT(varchar(X), datetimeValue, 131)

Where X is the total number of characters desired.

You will then be able to search for a match with datetimeValue and event_timestamp, assuming each value share the same structure. This will allow you to match string against string.

Robert
  • 8,717
  • 2
  • 27
  • 34
  • I'm still getting the same syntax error. Also, I'm trying to find intervals between the two fields, as the values are not supposed to be exactly the same. Would I be able to do this with two varchars? Sorry, I'm still new to SQL. – Stanley Ayzenberg Aug 08 '12 at 15:45
0

If I'm not mistaken the standard (ANSI SQL) CAST operator always expect time/date/timstamp literals in ISO format ('YYYY-MM-DD')

But according to the manual for Teradata V12 (can't test it), the format of the CAST operator is 

CAST(character_expression AS TIMESTAMP timestamp_data_attribute)

with date_data_attribute being a character value plus an optional FORMAT specifier.

So in your case this would probably be:

cast(event_timestamp AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS Z');

I'm not entirely sure about the format definition though. You'll probably need to adjust that

Btw: CONVERT isn't a standard SQL function. It's SQL Server specific.

  • Yeah, sorry, that was my mistake. And unfortunately, that didn't work either. Aster Data might handle it differently, because the reference documentation doesn't mention adding a FORMAT specifier as an option. I was told that they used standard ANSI SQL-92, but given some of the oddities I've found while working in it, I'm starting to doubt that... – Stanley Ayzenberg Aug 08 '12 at 17:25
  • @StanleyAyzenberg: why are you referring to "Aster Data" but have the question tagged "teradata"? –  Aug 08 '12 at 17:28
  • I actually didn't add that tag myself, it looks like it was added in by someone else. Besides, there is no tag for Aster Data. – Stanley Ayzenberg Aug 08 '12 at 17:39