convert a number to a 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert a number to a date

Author

Topic

sharonmtowler
Starting Member

12 Posts

Posted - 2010-05-04 : 12:52:53

i am attempting to convert a number field to a date
(it is actually a date field)genericdate(int)

i use the following
CONVERT(datetime,convert(char(12),dbo.SuspiciousActivity.BookDate ,101))as bkdate,
which converts it to a date time
i would like to convert it to a date field as this sp is used in crystal reports in a .net environment and i receive a conversion error. any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:56:17

what will be format of value in it? is it like yyyymmdd?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharonmtowler
Starting Member

12 Posts

Posted - 2010-05-04 : 13:16:24

yes yyyymmdd

quote:
Originally posted by visakh16

what will be format of value in it? is it like yyyymmdd?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:22:22

then do like below


SELECT genericdate AS intval,
DATEADD(dd,(genericdate%100)-1,DATEADD(mm,((genericdate%10000)/100)-1,DATEADD(yyyy,(genericdate/10000)-1900,0))) AS datetimeval
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharonmtowler
Starting Member

12 Posts

Posted - 2010-05-04 : 13:33:57

where do i place this above my select statement where i declare my parameters?
or the beginning of the select statement

?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:37:40

in your select statement replace the occurance of genericdate with second expression
i.e
DATEADD(dd,(genericdate%100)-1,DATEADD(mm,((genericdate%10000)/100)-1,DATEADD(yyyy,(genericdate/10000)-1900,0)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources