Creating dates from table columns

Hi, i have a table with 3 ints that are used to store dates. The
datetime data type is not used because this data comes from an old
AS400 server.

I need to be able to use those 3 columns to build dates within a query
and be able to use them to compare themselves to other dates

Let's say the table has the following values:

myday mymonth myyear
23 5 2006

and suppose i want to do a query that displays all rows with date
greater than '20060520'

Here is the query i have tried:

select
cast(myday as varchar(2))+'/'+cast(mymonth as
varchar(2))+'/'+cast(myyear as varchar(4))
from mytable

That query returns the string '23/5/2006' yet i can't use it to compare
it with '20060520'

Is there a way i can do this in a simple query?
This is on sql server 2000This seems to work but it is way ugly and depends heavily on the date
format:

select
cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
from mytable
where cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
between '20060520' and '20061231'

Also, i tried to use an alias on the select but then it doesn't get
recognized on the where so i had to type it full again.

Anyone knows a better way? Asp has a dateserial() method that
constructs a date given a month , day and year, but sql server doesn't
seem to have anything like that as a sql date function.|||fjleon@.gmail.com wrote:
> Hi, i have a table with 3 ints that are used to store dates. The
> datetime data type is not used because this data comes from an old
> AS400 server.

You mean via a linked server or do you import it? If it's the latter
then preferably fix the dates at import before they get into the
database.

Try:

SELECT myday, mymonth, myyear,
DATEADD(DAY,myday,
DATEADD(MONTH,mymonth,
DATEADD(YEAR,myyear-2000,'19991231')))
FROM mytable ;

This returns a DATETIME, not a string.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||> Try:
> SELECT myday, mymonth, myyear,
> DATEADD(DAY,myday,
> DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myyear-2000,'19991231')))
> FROM mytable ;
> This returns a DATETIME, not a string.

Seems to be one month forward:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

10520062006-06-10 00:00:00.000
20520062006-06-20 00:00:00.000

I had to substract a month:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

I am guessing this only works from y2k forward. I don't quite
understand why i have to substract a month, but at least it works, and
it's a lot cleaner than casting around.|||Do you know how to use that constructed date in a WHERE? dateadd....
as mydate where '20060501' <=p for example doesn't work

The weird thing is that in sql analizer it shows mydate as the name of
the column but if i use it in the where it fails|||(fjleon@.gmail.com) writes:
> Seems to be one month forward:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> 10 5 2006 2006-06-10 00:00:00.000
> 20 5 2006 2006-06-20 00:00:00.000
> I had to substract a month:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> I am guessing this only works from y2k forward.

No, it works for dates in the 1900s as well.

> I don't quite understand why i have to substract a month,

When you've added 5 months, you are at the end of May. Now you
add some days. That brings you into June.

> Do you know how to use that constructed date in a WHERE? dateadd....
> as mydate where '20060501' <=p for example doesn't work
> The weird thing is that in sql analizer it shows mydate as the name of
> the column but if i use it in the where it fails

It's not weird at all. You cannot use a column alias defined in a query
anywhere else in the query, except in the ORDER BY clause. However, you
can use a derived table - a query within the query. See the script
below. I've also modofied David's expression in a way that I think is
more robust.

CREATE TABLE fjleon(myday int NOT NULL,
mymonth int NOT NULL,
myyear int NOT NULL)

INSERT fjleon (myday, mymonth, myyear)
VALUES (23, 5, 2006)
INSERT fjleon (myday, mymonth, myyear)
VALUES (12, 7, 1996)
INSERT fjleon (myday, mymonth, myyear)
VALUES (29, 2, 2004)
INSERT fjleon (myday, mymonth, myyear)
VALUES (1, 3, 2004)
go
SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon
go
SELECT myday, mymonth, myyear, mydate
FROM (SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon) AS x
WHERE mydate > '20040101'
go
DROP TABLE fjleon

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you both, this works nice.