October 14, 2019 at 3:34 pm
Hi All,
We are loading data from one of our source table in that table createddate column datatype is char(22) and value is 2012-06-03 04:11:49:16. And in my target table the column datatype is datetime and we are using convert datetime function to convert char to datetime. But the date is converting to 2012-06-03 04:11:49:016. we have to load same value-2012-06-03 04:11:49:16 in target table also. Please suggest on this.
Thanks,
October 14, 2019 at 3:48 pm
You've discovered the hard way why it's not a good idea to store dates in a char column. That's not a criticism of you - I'm sure it's not your design!
Having three colons in your date isn't a valid format - at least not one that I've seen before. I don't know why it converts to a seemingly arbitrary value (on my computer it ends .017). But what you need to do is swap the last ":" for a "." where you have three colons, and then they will convert correctly.
John
October 14, 2019 at 3:50 pm
the best I can suggest is reverse the string, do a charindex for the first colon : then you know if there are only 2 digits for milliseconds
if it's 2, then add 0 the then end of the original string
MVDBA
October 14, 2019 at 4:10 pm
It seems that if you simply add a '0' to the end of your string, it converts correctly
DECLARE @charDateTime char(22) = '2012-06-03 04:11:49:16';
SELECT CONVERT(datetime, @charDateTime+'0', 121)
October 14, 2019 at 4:15 pm
You've discovered the hard way why it's not a good idea to store dates in a char column. That's not a criticism of you - I'm sure it's not your design!
Having three colons in your date isn't a valid format - at least not one that I've seen before. I don't know why it converts to a seemingly arbitrary value (on my computer it ends .017). But what you need to do is swap the last ":" for a "." where you have three colons, and then they will convert correctly.
John
It would appear that the hh:mi:ss:mmm is the European default time format.
CAST and CONVERT (Transact-SQL) See style 113 and 114
October 14, 2019 at 4:28 pm
You could try converting to a datetime2 data type - on my machine that produces the leading zero:
declare @dt varchar(30) = '2012-06-03 04:11:49:16'
select @dt = replace(@dt,'-','')
select @dt
select TRY_CAST(@dt as datetime2)
select TRY_CAST(@dt as datetime)
October 15, 2019 at 3:34 am
Ian
unless i misread the original post (I've done that a lot) , they want a trailing zero not a leading zero
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply