question

Msvelu avatar image
Msvelu asked

102535 this time format i stored in sql sever. I want to change this into HHMMSS

HI, I have a time_column (unfortunately datatype is decimal) .I have to concatenate one time_column to this date_column. This time column is also defined as a decimal ,I'm sure even its code is gonna be similar to the one below The original format is HHMMSS and I want it in HH:MM:SS.I have displayed the output below MY_time_column 123658 171420 102137 102704 111639 162821 105001 84814 120505 122223 170520 92403 93551
sqlserver2008
10 |1200

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

1 Answer

·
Sule avatar image
Sule answered
SELECT LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),2) + ':' + RIGHT(LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),4),2) + ':' + RIGHT(CAST(MY_time_column AS Varchar(10)), 2) FROM YourTable
6 comments
10 |1200

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

Hi Sule, Thanks for your answer. But I want to update the values into my sql server database......
0 Likes 0 ·
Well, then use UPDATE statement instead of SELECT.
0 Likes 0 ·
UPDATE mytime set LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),2) + ':' + RIGHT(LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),4),2) + ':' + RIGHT(CAST(MY_time_column AS Varchar(10)), 2); I tried like this, but it shows error like incorrect systax error near LEFT.....
0 Likes 0 ·
Your syntax for UPDATE statement is incorrect, try this but replace YOURCOLUMNNAME with real column name in your table: UPDATE mytime set YOURCOUMNNAME = LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),2) + ':' + RIGHT(LEFT(RIGHT('0000' + CAST(MY_time_column AS Varchar(10)), 6),4),2) + ':' + RIGHT(CAST(MY_time_column AS Varchar(10)), 2);
0 Likes 0 ·
Hi Sul.. It's working. Thank you thank you so much....
0 Likes 0 ·
Hi Sule please help in this case.. convert 103525.1235 into this format HHMMSS.NN in sql server2008 10:35:25.12
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.