integer to datetime UDF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

integer to datetime UDF

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-05 : 16:07:31

Trying to write the most effective UDF to convert INT to Datetime.
We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.

ALTER FUNCTION IntegerToDatetime (@int INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @IntegerToDatetime int
DECLARE @time DATETIME
SET @time = '2001-01-01'
SET @IntegerToDatetime =
CASE
WHEN LEN(@int) = 7
THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int)
WHEN LEN(@int) = 6
THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int)
WHEN LEN(@int) = 5
THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int)
WHEN LEN(@int) = 4 THEN cast(@time AS INT)

END
RETURN (@IntegerToDatetime )
END

GO

INPUT
-------------------------------
990831
981019

RESULT
-------------------------------
1900-01-02 00:00:00.000
1900-05-27 00:00:00.000

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-05 : 16:20:39

[code]DECLARE @Sample TABLE (i INT)

INSERT @Sample
SELECT 990831 UNION ALL
SELECT 19981019

SELECT i,
DATEADD(MONTH, 12 * CASE WHEN i / 10000 < 50 THEN i / 10000 + 2000 WHEN i / 10000 <= 99 THEN i / 10000 + 1900 ELSE i / 10000 END - 22801 + (i / 100) % 100, (i % 100) - 1)
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-05 : 16:44:18

Peso,
It works. What is the name of this formula?

http://www.sqlserverstudy.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-05 : 17:07:42

"Remember your math from school and read Books Online to learn how datetime related functions work"?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 17:08:58

quote:
Originally posted by Peso

"Remember your math from school and read Books Online to learn how datetime related functions work"?

That's just what I needed to start my weekend on a fun note!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-05 : 17:16:43

pESO,
it doesnt work for 1021122
1021122 = 2002-11-22, 1 means century 21. we also have 0 which means century 20.

http://www.sqlserverstudy.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-10-05 : 18:54:12

Awesome, that last one really made my day

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 04:24:34

Doesn't sound very Y2.01K compliant <g>

I wonder whether that maths route is the best choice Peso?

It will let through illegal dates (20010229 for example), and maybe something that bust the string up into Y,M,D parts, and presents them to SQL Server to convert will at least fail if an illegal date is passed - rather than silently converting it to a different date!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-06 : 06:32:32

quote:
Originally posted by funketekun

Peso,
It works. What is the name of this formula?

http://www.sqlserverstudy.com

Integer to datetime

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-06 : 06:34:45

quote:

Originally posted by Peso

DECLARE @Sample TABLE (i INT)

INSERT	@Sample
SELECT	990831 UNION ALL
SELECT	19981019

SELECT	i,
	DATEADD(MONTH, 12 * CASE WHEN i / 10000 < 50 THEN i / 10000 + 2000 WHEN i / 10000 <= 99 THEN i / 10000 + 1900 ELSE i / 10000 END - 22801 + (i / 100) % 100, (i % 100) - 1)
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

The values 0000000, 888888, etc are also converted to dates

Madhivanan

Failing to plan is Planning to fail

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-06 : 23:57:52

Any other plan?

http://www.sqlserverstudy.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-07 : 00:30:03

here's one way that doesn't allow invalid dates. I'm embarrassed to post it but what the hell.


DECLARE @Sample TABLE (i INT)

INSERT	@Sample
select  1001231 union all -- leading 1 means from 21st century according to OP
select  1111231 union all -- leading 1 means from 21st century according to OP
select  0990831 union all -- leading 0 means from 20th century
select  19981019          -- unambiguous

select convert(datetime, 
   case when i between 1000000 and 1999999 then convert(datetime, '20' + right('000000' + convert(varchar,i),6)) 
        when i between 0 and 999999 then convert(datetime, '19' + right('000000' + convert(varchar,i),6))
        else convert(datetime, convert(varchar,i)) end) 
from @sample 

-- now try madhi's invalid dates
delete @sample
insert @sample 
select 888888 union all 
select 000000

select convert(datetime, 
   case when i between 1000000 and 1999999 then convert(datetime, '20' + right('000000' + convert(varchar,i),6)) 
        when i between 0 and 999999 then convert(datetime, '19' + right('000000' + convert(varchar,i),6))
        else convert(datetime, convert(varchar,i)) end) 
from @sample

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-07 : 03:23:07

So there will be

1) yymmdd
2) yyyymmdd
3) cyymmdd

All these three are mixed in same column?

c = century 0 for 1900, 1 for 2000
yy = two last digits of the year
mm = month
dd = day

Are there any more combinations that I have missed?

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-07 : 19:51:03

No, I think they are all.

http://www.sqlserverstudy.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 02:42:38

Also try to simulate this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 04:00:31

[code]DECLARE @Sample TABLE (i VARCHAR(8))

INSERT @Sample
SELECT '1001231' UNION ALL
SELECT '0111130' UNION ALL
SELECT '19981019' UNION ALL
SELECT '20060330' UNION ALL
SELECT '980719' UNION ALL
SELECT '070228'

SELECT i AS OriginalText,
DATEADD(MONTH, 12 * theYear - 22801 + theMonth, theDay - 1) AS theDate
FROM (
SELECT i,
CASE
WHEN LEN(i) = 8 THEN CAST(LEFT(i, 4) AS SMALLINT)
WHEN LEN(i) = 7 THEN CAST(1900 + 100 * LEFT(i, 1) + SUBSTRING(i, 2, 2) AS SMALLINT)
WHEN LEN(i) = 6 AND i <= '491231' THEN CAST('20' + LEFT(i, 2) AS SMALLINT)
WHEN LEN(i) = 6 AND i <= '991231' THEN CAST('19' + LEFT(i, 2) AS SMALLINT)
ELSE CAST(NULL AS SMALLINT)
END AS theYear,
CAST(LEFT(RIGHT(i, 4), 2) AS TINYINT) AS theMonth,
CAST(RIGHT(i, 2) AS TINYINT) AS theDay
FROM @Sample
WHERE i NOT LIKE '%[^0-9]%'
AND LEN(i) BETWEEN 6 AND 8
) AS t
WHERE theYear IS NOT NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 04:30:40

"Also try to simulate this"

That's a very handy function Madhi!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 06:04:48