How to get date value from a number ( ex: 20161108) ?

Question

Post reply

How to get date value from a number ( ex: 20161108) ?

prabhu.st

SSCrazy

Points: 2552
More actions
November 8, 2016 at 6:48 am

#315406

Hi,
I have this "20161108", I need this "2016-11-08 00:00:00.000", may be this pretty simple, but I am getting error when I try the below
select convert(datetime,convert(float,20161108))
please help.
Thanks,
Prabhu

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

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

Michael L John One Orange Chip Points: 27053 More actions · Answer 1

Converting to a float is the problem.

Try converting to a varchar(10) instead of a float.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It's not that simple.

Casting the float to datetime directly gets you 20161108 days after the 1st of January 1900, which, since 20161108/365.25 leaves us with 55 thousand years, exceeds the max for datetime of 31 December 9999 by roughly 47 millennia.

Cast (or convert if you prefer) through string first.

DECLARE @BadDate INT = 20161108;

SELECT CAST(CAST(@BadDate AS CHAR(8)) AS DATETIME)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246944 More actions · Answer 3

And as you have no time component, consider using the DATE datatype, rather than DATETIME ... if you do, you'll save 5 bytes per occurrence.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

Assuming you want to convert a string to DateTime.

CAST('20161108' AS DATETIME)

If the string contains a value that cannot be converted to a true calendar date (ie: Feb 29, 2015), then the function will raise a conversion error.

Alternately you can use the TRY_CAST function, which will return a result of NULL if the conversion doesn't work.

TRY_CAST('20161108' AS DATETIME)

Also, as mentioned above, a simple date value without time portion can be cast as Date rather than DateTime.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

prabhu.st SSCrazy Points: 2552 More actions · Answer 5

Hi All,

the problem was on converting the value into float as micheal and gail said, the code given by gail works for me.

Thanks gail and everyone for your quick response.

-Prabhu