Convert Int to Date

[Artie]

Hi,
I have a date field stored as an INT and need to convert to a date format
mm/dd/yyyy. I'm having trouble determining what the starting date is.
min(date) = 730395
max(date) = 733189

Starting at 01/01/1900, 730395 would make the min year 3901?

Thanks for any help.

[--CELKO--]

>I have a date field [sic: columns are not fields] stored as an INTEGER and need to convert to a date format [sic: temporal data is not stored formatted] has mm/dd/yyyy. I'm having trouble determining what the starting date is. <<

Before anyone can help you, you need to understand your own data.
However, once you find out what your minimum date is, then you can
build a table with (integer, datetime) columns and do a simple look
up.

Fields have formatting (think COBOL, punch cards, etc.) while columns
have a data type (think abstract data models). The reason that you
have to deal with this crap is that someone did not know the
difference.

[Erland Sommarskog]

Artie (ar*******@yahoo.com) writes:

I have a date field stored as an INT and need to convert to a date format
mm/dd/yyyy. I'm having trouble determining what the starting date is.
min(date) = 730395
max(date) = 733189

Starting at 01/01/1900, 730395 would make the min year 3901?

As Celko said, you need to know your data, and we cannot really help you
on that point. You need to consult the documentation for this database,
or find someone who knows about it.

That said, I ran this on SQL 2008:

select dateadd(day, 730395, convert(date, '0001-01-01'))
select dateadd(day, 733189, convert(date, '0001-01-01'))

And I got back:

2000-10-03
2008-05-28

Which certainly looks compelling. But again, you need to verify.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Artie]

Problem is this is not my data and nobody can find any docs on this. This
is from an old version of Adapt CRM. Adapt is still around but the cheapos
I work for won't pay for support :-(

I did not think to use 0001-01-01 as a startdate since SQL was not around in
Biblical times!
Thanks for the help. You certainly got me going in the right direction.


"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Artie (ar*******@yahoo.com) writes:

>I have a date field stored as an INT and need to convert to a date format
mm/dd/yyyy. I'm having trouble determining what the starting date is.
min(date) = 730395
max(date) = 733189

Starting at 01/01/1900, 730395 would make the min year 3901?

As Celko said, you need to know your data, and we cannot really help you
on that point. You need to consult the documentation for this database,
or find someone who knows about it.

That said, I ran this on SQL 2008:

select dateadd(day, 730395, convert(date, '0001-01-01'))
select dateadd(day, 733189, convert(date, '0001-01-01'))

And I got back:

2000-10-03
2008-05-28

Which certainly looks compelling. But again, you need to verify.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Artie (ar*******@yahoo.com) writes:

Problem is this is not my data and nobody can find any docs on this.
This is from an old version of Adapt CRM. Adapt is still around but the
cheapos I work for won't pay for support :-(

Nevertheless, all we can offer here are guesses. Hopefully, you are able
to correlate the data with real-world information to confirm the
hypothesis.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>I did not think to use 0001-01-01 as a startdate since SQL was not around in Biblical times! <<

I remember that! When I was consulting for Moses, I told him those
were lousy business rules :)

Erland has a good guess; Oracle can handle dates in the CE and BCE
ranges, so the package might have be ported over that product.