|
|
Posted by Erland Sommarskog on 09/29/88 11:48
(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|