Integer 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

Integer to Date

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2008-07-23 : 01:10:54

Hi guys,

I'm making a program in VB.net and SQL Server 2000. I imported a file which came from a Clarion database. The table has a column name EffectiveDate. When I openned the database from a Clarion the dates are in numerical figures, not dates. But when I ran the front-end (Clarion) of the program (which I dit not make it), the numeric figures are shown in date formats.

After I imported files, the data type is integer (SQL Server).

My question here is, how can I conver these numeric figures into date type?


EffectiveDate

70784
70966
70784
71835
72016
71835
70730
70914
74223
72843
71017
71017
74238
74471
72322

Thank you

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-07-23 : 01:25:49

Typically, dates stored as integers are the number of days since some zero date. For example, in SQL Server, the zero date is 1/1/1900. I don't know what the zero date is for Clarion, but you can probably use Google to find out and then use the DATEADD function in SQL to add the integer number of days to the zero date and return a true date value.

---------------------------
EmeraldCityDomains.com

rpc86
Posting Yak Master

200 Posts

Posted - 2008-07-23 : 02:43:12

someone has advice me this code...


Set the SQLDate = 73603  ! todays date in long format.

Select
DateADD(Day,((DateDiff(Day,'0001/01/01','1800/12/28')+SQLDATE)),'0001/01/01'
)

This returns '2002-07-05'  which is correct...

but...

when I tried, this is my error:


Declare @SQLDate Int
set @SqlDate=73603
Select DateADD(Day,((DateDiff(Day,'01/01/1900','01/01/1900')+@SqlDate)),'0001/01/01')


Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

rpc86
Posting Yak Master

200 Posts

Posted - 2008-07-23 : 02:48:14

OK. I Resolved it now.

it should be..


DateADD(Day,((DateDiff(Day,'1900/01/01','1800/12/28')+@sqlDate)),'1900/01/01')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources