asked
datetime question
How to convert date time in 140312075012 to '03/14/2012 04:00' in sqlserver Thanks
Can you please add more details how you are translating 140312075012 to 03/14/2012 04:00? If the time portion is to be hard coded with 04:00 and assuming the year part is always for the twentieth century then one way of doing it, is SELECT SUBSTRING(@date, 3, 2) + '/' + LEFT(@date, 2) + '/20' + SUBSTRING(@date, 5, 2) + ' 04:00'
The confusion was due to the original question's translation of 075012 to 04:00... Follow and extend @Usman Butt's suggestion.
Well, here's one way of doing it. It's not particularly elegant: declare @dt bigint select @dt = 140312075012 declare @dtString char(12) select @dtString = CONVERT(char(12), @dt) declare @isoString char(20) select @isoString = '20' + substring(@dtString,5,2) + '-' + SUBSTRING(@dtString,3,2) + '-' + substring(@dtString,1,2) + ' ' + substring(@dtstring,7,2) + ':' + substring(@dtstring,9,2) + ':' + SUBSTRING(@dtstring,11,2) select @dt, @dtString, @isoString, convert(datetime, @isoString) What I'm doing is converting your string using the SubString function into a string in ISO8601 format (yyyy-mm-dd hh:mm:ss), and then using the Convert function to display the date as a datetime field rather than as a string field. If you wanted to actually use the datetime value that you've just calculated elsewhere, then you could: declare @ProperDateTime datetime SELECT @ProperDateTime = convert(datetime, @isoString) and then go on to use that elsewhere in your query.
5 People are following this question.
