Menu
I have tried the links available here for similar questions before but it does not work. So I am posting my case here.
I have time in format:
09:47:11:895799
This is stored as varchar(50) in my Microsoft SQL Server database.
I am trying to convert it to time format as on this link.
hh:mm:ss[.nnnnnnn]
Can you please help?
388
How do I change the datatype of a column from varchar to date in SQL? Add a column to your table to hold the DATE data. ALTER TABLE my_table ADD (new_col DATE); Set this new column's value to hold the old_column's value converted to a DATE value.
You can store date in a VARCHAR column, you can also store non-dates in that VARCHAR column.
Use the function TO_DATE() to convert a text value containing a date to the date data type. This function takes two arguments: A date value. This can be a string (a text value) or a text column containing date information.
Answer: The error is due to an invalid date format being saved to the custom_rmh_rooms_history SQL table. To resolve this issue, the Windows Regional settings need to be modified and the Short Date format needs to be in MM/dd/yyyy format.
The string is in the wrong format. It should be 09:47:11.895799. You need to replace the final : with a .
SELECT CAST(STUFF('09:47:11:895799', 9,1,'.') AS TIME)
You can also make your string as hh:mi:ss:mmm (24 hour format) and then convert as time. That means trimming out last 3 digits from your milli seconds without replacing last ':' with '.'
Here is MSDN link for more details of formatting. Here is working example of SQL Fiddle
DECLARE @s varchar(50) = '09:47:11:895799'
SELECT Convert(time, Left(@s,Len(@s)-3), 114)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
