Errors while trying to cast/convert VARCHAR to DATETIME in ANSI SQL

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.