question

katie 2 avatar image
katie 2 asked

Nvarchar conversion changing datetime from style 120 to 109

I am doing an up date statement like the following: update table set table.dateUpdated=convert(datetime,table2.d1,120) from table inner join table2 on table.id= table2.id where table.dateupdated is null table.dateupdated is nvarchar(255) so when the update happens it's converting the datetime to style 109. Here's a test: select convert(datetime,'9/16/2011',120) select cast(convert(datetime,'9/16/2011',120) as nvarchar(255)) How do I get the style to stay at 120 with in the nvarchar datatype?? Thanks! K
sql-server-2005datetimecast-convert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
To get a style in the nvarchar, you need to specify it when converting to the nvarchar, so: select convert(nvarchar,(convert(datetime,'9/16/2011',120)),120) gives 2011-09-16 00:00:00 in fact the inner convert to 120, is un-needed select convert(nvarchar,(convert(datetime,'9/16/2011')),120) --or select convert(nvarchar,(cast('9/16/2011' as datetime)),120) both give 2011-09-16 00:00:00
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ok, so when I do the update the above scenario does not work but if I do a select it does. I need the value stored to be in the style 120 format, so i need to get it working in the update statement. update table set Phase_1_Date_Delivered=convert(datetime,Phase_1_Date_Delivered,120) This still converts back to style 109. Thanks for your help!
0 Likes 0 ·
just tried the double convert again and I must have been doing something wrong because it appears to have converted properly.
0 Likes 0 ·
double checked and it's for sure for sure! :) Thanks so much. Fyi..I did the double convert method.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.