March 26, 2007 at 2:02 pm
Is there a way to convert a varchar to datetime when the string contains the time? Please tell me I don't have to parse it out!
DECLARE @RdeStartDate datetime
set
@rdestartDate = CONVERT(datetime, '200609151540') --(fails when string contains time)
set
@rdestartDate = CONVERT(datetime, '20060915') --(else it works)
March 26, 2007 at 3:15 pm
The data needs to be in a format SQLServer denotes as datetime.
'200609151540' is not a datetime it is a string
'20060915 15:40' is recognizable as a datetime string, with rules to convert.
Parsing is probably required but shouldn't be too killer and doable inline
If you know your initial string '200609151540' targetString
select left(targetString, 8) + ' ' + left(right(targetString, 4),2) + ':' + right(targetString,2)
from yada
If you need to have this requirement in multiple areas it would be straight-forward to create a function that takes your datetime string '200609151540' and returns a recognizable datetime string '20060915 15:40'. Note: error catching for '200702312578' would be advisable.
daralick
March 26, 2007 at 4:22 pm
Yuck, but ok, that's kind of what i thought. FWIW, here's what i came up with, made even uglier by the fact that there may or may not be seconds, milliseconds.
Thanks!
DECLARE
@RdeStartDate datetime
IF NOT ISDATE(@inDate) = 1
BEGIN
IF LEN(@inDate) = 12 --assume yyyymmddhhMM
SET @inDate = LEFT(@inDate, 8) + ' ' + SUBSTRING(@inDate, 9, 2) + ':' + RIGHT(@inDate,2)
ELSE IF LEN(@inDate) = 14 --assume yyyymmddhhMMss
SET @inDate = LEFT(@inDate, 8) + ' ' + SUBSTRING(@inDate, 9, 2) + ':' + SUBSTRING(@inDate, 11, 2) + ':' + RIGHT(@inDate,2)
ELSE IF LEN(@inDate) > 14 --assume above plus some number of millisecond digits
SET @inDate = LEFT(@inDate, 8) + ' ' + SUBSTRING(@inDate, 9, 2) + ':' + SUBSTRING(@inDate, 11, 2) + ':' + SUBSTRING(@inDate, 13, 2) + '.' + RIGHT(@inDate, LEN(@inDate) - 14)
END
SET @rdestartDate = CONVERT(datetime, @inDate)
March 30, 2007 at 10:05 am
If all you care about in the string is the date portion, then you could just substring off the first chartacters only, and the time will/should default to Midnight. If you don't care about the time, then this is a prettier fix.
Brian
March 30, 2007 at 12:31 pm
This seems simpler and looks like it works OK.
select [DateTime] = convert(datetime,substring(MyDate,1,8))+ -- Add hours dateadd(hh,0+substring(MyDate,9,2),0)+ -- Add minutes dateadd(mi,0+substring(MyDate,11,2),0)+ -- Add seconds dateadd(ss,0+substring(MyDate,13,2),0)+ -- Add millseconds, and right fill with zeros dateadd(ms,0+left(substring(MyDate,15,3)+'000',3),0) from (-- Test Data Select Mydate = '20071221234455' union all Select Mydate = '200712212243' union all Select Mydate = '2007122121' union all Select Mydate = '20071221' union all Select Mydate = '20071231224354123' union all Select Mydate = '2007123121425312' union all Select Mydate = '200712312041511' ) a
Results:
DateTime ----------------------- 2007-12-21 23:44:55.000 2007-12-21 22:43:00.000 2007-12-21 21:00:00.000 2007-12-21 00:00:00.000 2007-12-31 22:43:54.123 2007-12-31 21:42:53.120 2007-12-31 20:41:51.100
(7 row(s) affected)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply