January 18, 2013 at 3:33 am
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
January 18, 2013 at 4:47 am
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
January 18, 2013 at 5:23 am
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)
January 18, 2013 at 6:30 am
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
January 18, 2013 at 7:57 am
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/
January 18, 2013 at 8:34 am
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
January 18, 2013 at 9:02 am
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) + ' '+
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)
Else if (LEN(@Date) = 8 and right(@Date,4) between '1900' and '2020')
set @FullDttm =
left(@Date, 2) + '/' + substring(@Date, 3,2) + '/' + right(@Date,4) + ' '+
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)
Else if (LEN(@Date) = 6 )
if (RIGHT(@Date,2) > 20)
set @FullDttm = LEFT(@Date,2) + '/' + substring(@Date,3,2) + '/' + '19' + RIGHT(@Date,2) + ' ' +
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)
else
set @FullDttm = LEFT(@Date,2) + '/' + substring(@Date,3,2) + '/' + '20' + RIGHT(@Date,2) + ' ' +
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)
ELSE if (LEN(@Date) = 5 )
if (RIGHT(@Date,2) > 20)
set @FullDttm = '0' + LEFT(@Date,1) + '/' + substring(@Date,2,2) + '/' + '19' + RIGHT(@Date,2) + ' ' +
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)
else
set @FullDttm = '0' + LEFT(@Date,1) + '/' + substring(@Date,2,2) + '/' + '20' + RIGHT(@Date,2) + ' ' +
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)
ELSE if (LEN(@Date) = 7 and LEFT(@Date,4) between '1900' and '2020' )
set @FullDttm = '0' + substring(@Date,5,1) + '/' + RIGHT(@Date,2) + '/' + LEFT(@Date,4) + ' ' +
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)
ELSE if (LEN(@Date) = 7 and RIGHT(@Date,4) between '1900' and '2020' )
set @FullDttm = '0' + LEFT(@Date,1) + '/' + substring(@Date,2,2) + '/' + RIGHT(@Date,4) + ' ' +
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)
RETURN (@FullDttm)
END;
GO
I agree with what you saying ....I have been keep fighting with it.....The customer is not allowing to update the column to datetime....so only way i find is to create a function which supports all kind of format......I created this function with the challenges i faced with the data....
Please take a look at it and please gimme your valuable suggestions so that i would improve myself in future....
Thanks,
Charmer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply