you are converting to datetime and then back to string concatenate and then convert back to datetime again :(if you are using Ifor method, you might was well just use dateadd() to add to the date1 select dateadd(hour, floor(time1 / 10000), dateadd(minute, floor(time1 / 100) % 100, dateadd(second, time1 % 100, date1))) you don't have to convert date1 to string and then concatenate with the time string2. Khtan,Thanks ! I figured it out about the same time as your postCONVERT(DATETIME,CONVERT(VARCHAR (12), date1) + ' ' + CONVERT(VARCHAR (8),(CAST(DATEADD(SECOND, FLOOR(time1/ 10000) * 3600 + FLOOR(time1 / 100) % 100 * 60 + time1 % 100, 0) AS DATETIME)) ,108), 108) If the time is already in string, then you can just use ADD to do it select Date1 + stuff(stuff(right('000000' + convert(varchar(6), time1), 6), 3, 0, ':'), 6, 0, ':') I used the followingCONVERT(VARCHAR (8),CAST(DATEADD(SECOND, FLOOR(time1/ 10000) * 3600 + FLOOR(time1 / 100) % 100 * 60 + time1% 100, 0) AS DATETIME) ,108)Which actually does bring me to another question since I cant do any calculations off the time as a string.I do like the logic that Ifor gave which brings me to another question.if I use his logic I get the 14:53:13.000 - bascially the if I have another field that is Datetime and showsField is called Date1 00:00:00.000What is the best way for me to combine the 2 so I get a true Datetime fieldSo if combined it would show 14:53:13.000 Khtan, I am able to get it back as a sting. If you want the time to return as string select stuff(stuff(right('000000' + convert(varchar(6), time1), 6), 3, 0, ':'), 6, 0, ':') That would make sense why the other methods that I had tried didnt work.Ĭheck if this works for you declare varchar(10)set (Som, Ni, Yak) On my machine I'm running R2, but I'll have to follow up on the server. I guess I'll just have to substring the data out because this is what that returns 09:40:46.000Īre you using SQL Server 2008 ? time data type is only available from SQL 2008 onwards IFor, When I leave "Time" in sql server it doesnt like it and says it's not a system typeMsg 243, Level 16, State 1, Line 14Type TIME is not a defined system type.However if I put Datetime in it seems to work. ![]() I have a time field thats calledtime1 it's a Decimal 6,0 it is currently in an hhmmss format just not a "Time" constraint What is the best way to convert it to hh:mm or hh:mm:ssit is on a 24 hour clock and in the early mornings the leading 0's do not come across for instnace 46 is 00:00:4694046 = 09:40:46ĭECLARE decimal(6) = 94046SELECT CAST(DATEADD(second, / 10000) * 3600 + / 100) % 100 * 60 + % 100, 0) as time) We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |