ISO dates?

[php newbie]

To support an application, I am integrating it with with Sqlserver
2000. The task is simple: get the data at regular intervals, and
write it into a table dedicated for that application. One of the
fields I receive is a date, and it is formatted as an ISO date. A
sample value looks like this: "2004-07-09-15.37.52.077258".

SQLServer doesn't support this date format, though. The closest I
have found is the style code "21" or "121" which accepts input as
"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()
and convert() calls but no solution yet.

Is there a way to transform the data to conform to this style accepted
by Sqlserver? I would like to do this in the INSERT statement itself
and not have to code application logic (i.e., user-defined function,
etc.) for it.

[Gert-Jan Strik]

No, not without using complex CAST functions, UDFs, or INSTEAD OF
triggers.

I suggest you stick to the simplified ISO 8601 syntax, for example
"20040709 15:37:52.077". If you must, you can use
"2004-07-09T15:37:52.077", which will also alway work correctly on
version 2000 of SQL-Server.

Also note that SQL-Server will not accept more than 3 digits for
milliseconds.

HTH,
Gert-Jan
php newbie wrote:

To support an application, I am integrating it with with Sqlserver
2000. The task is simple: get the data at regular intervals, and
write it into a table dedicated for that application. One of the
fields I receive is a date, and it is formatted as an ISO date. A
sample value looks like this: "2004-07-09-15.37.52.077258".

SQLServer doesn't support this date format, though. The closest I
have found is the style code "21" or "121" which accepts input as
"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()
and convert() calls but no solution yet.

Is there a way to transform the data to conform to this style accepted
by Sqlserver? I would like to do this in the INSERT statement itself
and not have to code application logic (i.e., user-defined function,
etc.) for it.

--
(Please reply only to the newsgroup)

[Aaron W. West]

You can't store time with higher resolution than 1/300th second (in
multiples of .010 + .000, .003 or .007) in a DATETIME column. Also, I think
it's generally better to do this in application logic, unless you have many
places where the time values are inserted. For example, Perl can do a lot
more with regular expressions than you can easily implement in T-SQL.

But this should work if the format never varies (eg. if your months and day
values are always 2 digits each, and the year always 4 digits)

declare @dt varchar(26)
set @dt = '2004-07-09-15.37.52.077258'
print left(@dt,10)+' '+replace(substring(@dt,12,12),'.',':')
select cast(left(@dt,10)+' '+replace(substring(@dt,12,12),'.',':') as
datetime)

"php newbie" <ne**********@yahoo.com> wrote in message
news:12**************************@posting.google.c om...
To support an application, I am integrating it with with Sqlserver
2000. The task is simple: get the data at regular intervals, and
write it into a table dedicated for that application. One of the
fields I receive is a date, and it is formatted as an ISO date. A
sample value looks like this: "2004-07-09-15.37.52.077258".

SQLServer doesn't support this date format, though. The closest I
have found is the style code "21" or "121" which accepts input as
"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()
and convert() calls but no solution yet.

Is there a way to transform the data to conform to this style accepted
by Sqlserver? I would like to do this in the INSERT statement itself
and not have to code application logic (i.e., user-defined function,
etc.) for it.