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.

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)

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" <newtophp2000@yahoo.com> wrote in message
news:124f428e.0407091204.34f6390b@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.