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
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
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!
