Converting Float to Date - 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

Converting Float to Date

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-03-06 : 13:06:04

Hi All - I have a column which I need to convert to datetime, which is currently encoded as FLOAT:

20120117
20120118
20120119

Any Ideas?

SELECT STATUS_DATE FROM TABLE T1

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 13:42:19

how's about


SELECT STATUS_DATE, ISDATE(STATUS_DATE), ISDATE(CONVERT(int,STATUS_DATE))
	 , CASE WHEN ISDATE(CONVERT(int,STATUS_DATE)) = 1 
			THEN CONVERT(int,STATUS_DATE)
			ELSE null
	   END  AS dtSTATUS_DATE
FROM (
SELECT CONVERT(float, '20120117') AS STATUS_DATE UNION ALL
SELECT CONVERT(float, '20120118') AS STATUS_DATE UNION ALL
SELECT CONVERT(float, '20120119') AS STATUS_DATE) AS X

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-06 : 17:37:49

You can use the CAST and Convert functions or you might be able to join to a Date table. If you can it's be better to change to a proper datatype rather than doing conversion on the fly.

At anyrate, here are some different ways to cast/convert the data type:

DECLARE @Foo TABLE (DateNumber FLOAT)
INSERT @Foo
VALUES 
    (20120117),
    (20120118),
    (20120119)

SELECT
    CONVERT(DATETIME, CAST(CAST(DateNumber AS INT) AS VARCHAR(8)), 112) 
FROM @Foo


SELECT 
    DATEADD(MONTH,((LEFT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)), 4)-1900)*12)+LEFT(RIGHT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)),4), 2)-1,RIGHT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)), 2)-1)
FROM @Foo

SELECT 
    CAST(LEFT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)), 4) + '-' + LEFT(RIGHT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)),4), 2) + '-' + RIGHT(CAST(CAST(DateNumber AS INT) AS VARCHAR(8)), 2) AS DATETIME)
FROM @Foo

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 20:47:45

D'oh...you just need to conert to int, then date or datetime..I left that out...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources