June 7, 2013 at 9:51 am
I need to add time to a value. The value is stored as char(4) - example '0630'.
I know to use the dateadd function but how do I format the '0630' into an acceptable parameter?
June 7, 2013 at 10:01 am
This should do it:-
SELECT STUFF('0630',3,0,':')
, CONVERT(time, STUFF('0630', 3,0,':'), 14)
In fact you don't even need to specify the 14 as the type of conversion; that's just me having a hangover from using datetime rather than time datatypes.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 7, 2013 at 10:05 am
Rog Saber (6/7/2013)
I need to add time to a value. The value is stored as char(4) - example '0630'.I know to use the dateadd function but how do I format the '0630' into an acceptable parameter?
And if at all possible change your datatype to datetime instead of character data. You will constantly be battling character data. It has to be casted or converted for everything and you will end up with values that are not valid. Performance will suffer because you can't leverage indexes...the list goes on and on.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 10:17 am
Thanks for the help Matthew.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply