Converting numeric to datetime

Question

Post reply

Converting numeric to datetime

Charmer

SSChampion

Points: 14903
More actions
January 18, 2013 at 3:33 am

#272913

Hi Friends,
I have a scenario to convert the numeric column to time. Below are the scenarios i could get.
ID Date Time RESULT (DateTime)
1 20101010 124556 = 2010-10-10 12:45:56:000
2 20101010 52834 = 2010-10-10 05:28:34:000
3 20101010 5548 = 2010-10-10 00:55:48:000
4 20101010 812 = 2010-10-10 00:08:12:000
5 20101010 58 = 2010-10-10 00:00:58:000
I am working on creating a function to pass these two columns as parameters to convert into datetime values....
Could you help me guys to get done my function..?
Any suggestions would be really appreciated.
Thanks,
Charmer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

No the most elegant solution but it works

create function dbo.fn_DateAndTime (@Date varchar(8), @Time varchar(6))

returns table as

return

select theDateAndTime = convert(datetime, @Date) +

cast(substring(RIGHT('000000' + CAST(@Time AS VARCHAR), 6), 1, 2)+ ':'+

substring(RIGHT('000000' + CAST(@Time AS VARCHAR), 6), 3, 2)+ ':' +

substring(RIGHT('000000' + CAST(@Time AS VARCHAR), 6), 5, 2) as time)

with dates(someDate, sometime )

as ( select '20101010', '124556' union all

select '20101010', '52834' union all

select '20101010', '5548' union all

select '20101010', '812' union all

select '20101010', '58' )

select somedate, sometime, theDateAndTime

from dates

cross apply dbo.fn_DateAndTime (somedate, sometime)

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

Another one.

select

d,

cast(stuff(stuff(replace(d, ' ', ' ' + replicate('0', 15 - len(d))), 14, 0, ':'), 12, 0, ':') as datetime) dt

from

(

values('20100228 124556'), ('20101231 52834'), ('20100401 5548'), ('20101130 812'), ('20101231 58')

) dates(d)

Charmer SSChampion Points: 14903 More actions · Answer 3

Thanks to Pete and Abu...Those scripts really works....

Friends...some times i see the date comes in different order....For Ex: instead of 20100225, it comes like 02252010..So my function get failed...Do we have any way to handle the different format of the date... ?

Thanks,
Charmer

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Charmer (1/18/2013)
Thanks to Pete and Abu...Those scripts really works....
Friends...some times i see the date comes in different order....For Ex: instead of 20100225, it comes like 02252010..So my function get failed...Do we have any way to handle the different format of the date... ?

You are simply going to have to know the format that your data is coming in. Your example above becomes even more challenging if the data is 20101111 or 11201011. What about this one 12121212 (december 12th in the year 1212). No matter which order the data is it will parse to a date but you have no idea which part is what. If you can pass in the int and the format you can do it. Otherwise you will constantly be fighting it. Of course the absolute best thing would be to change the original datatype to datetime, then you don't have to worry about this at all.

_______________________________________________________________

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/

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

I agree with Sean that there is no bullet-proof solution for an arbitrary input set. However, if the number of dates in the input is large enough and distributed, one can scan the entire set of dates to determine the date format.

Here's an example with the following assumptions:

- Date's are after 1900-01-01

- Only one of three date formats is expected: yyyymmdd, ddmmyyyy and mmddyyyy

with dates as

(

select

d

from

(

values('20100228 124556'), ('20101231 52834'), ('20100401 5548'), ('20101130 812'), ('20121212 58') -- yyyymmdd

--values('28022010 124556'), ('31122010 52834'), ('01042010 5548'), ('30112010 812'), ('12122012 58') -- ddmmyyyy

--values('02282010 124556'), ('12312010 52834'), ('04012010 5548'), ('11302010 812'), ('12122012 58') -- mmddyyyy

--values('01092010 124556'), ('02082010 52834'), ('03072010 5548'), ('04062010 812'), ('05052012 58') -- ????yyyy

) dates(d)

)

select

date_offsets,

cast(substring(d, date_offsets % 10, 4) + substring(d, (date_offsets % 100) / 10, 2) + substring(d, date_offsets / 100, 2) +

stuff(stuff(stuff(replace(d, ' ', ' ' + replicate('0', 15 - len(d))), 14, 0, ':'), 12, 0, ':'), 1, 8, '') as datetime) dt

from

dates

cross join

( -- Determine dateformat (returns a single row)

-- yyyymmdd

-- ddmmyyyy

-- mmddyyyy

select

case

-- yyyymmdd

when count(case when left(d, 4) >= '1900' then 1 end) = count(*)

and count(case when substring(d, 3, 2) between '01' and '12' then 1 end) = count(*)

and count(case when substring(d, 5, 2) between '01' and '31' then 1 end) = count(*) then

751

-- ddmmyyyy

when count(case when substring(d, 5, 4) >= '1900' then 1 end) = count(*)

and count(case when substring(d, 3, 2) between '01' and '12' then 1 end) = count(*)

and count(case when left(d, 2) between '01' and '31' then 1 end) = count(*) then

135

-- mmddyyyy

when count(case when substring(d, 5, 4) >= '1900' then 1 end) = count(*)

and count(case when left(d, 2) between '01' and '12' then 1 end) = count(*)

and count(case when substring(d, 3, 2) between '01' and '31' then 1 end) = count(*) then

315

else

NULL -- failure

end date_offsets -- day offset * 100 + month offset * 10 + year offset

from

dates

) cj

Charmer SSChampion Points: 14903 More actions · Answer 6

Create FUNCTION [dbo].[GetDateTime](@Date varchar(8), @Time varchar(6))

RETURNS datetime

WITH EXECUTE as CALLER

as

BEGIN

DECLARE @FullDttm datetime

If

(@Date = '0' and (@Time = '0' or @Time = '000000')) or (LEFT(@Date,6) = '190000' and LEN(@Date) = 8 ) or @Date = '0'

--or (LEFT(@Date,4) > '2012' and LEN(@Date) = 8 )

Set @FullDttm = '1/1/1900'

Else if (LEN(@Date) = 8 and left(@Date,4) between '1900' and '2020')

set @FullDttm =

substring(@Date, 5,2) + '/' + substring(@Date, 7,2) + '/' + left(@Date,4) + ' '+