Select condition with converted values

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 2000 Forums

Transact-SQL (2000)

Select condition with converted values

Author

Topic

homerjay80
Starting Member

26 Posts

Posted - 2007-05-03 : 17:06:33

Hello,

I have a working query that outputs the first day of the month after the 1 year annviersary of given date:

Select SSN ,
CONVERT(CHAR(10),CONVERT(DATETIME,RTRIM(CONVERT(CHAR(2),MONTH(DATEADD(MM,1,DATEADD(YY,1,Date))))) + '/1/' +
RTRIM(CONVERT(CHAR(4),YEAR(DATEADD(MM,1,DATEADD(YY,1,Date)))))),101) as Start
FROM Tables..

Now what I would like to do is add a where clause that narrows dates that are less than today.

I tried

Select SSN ,
CONVERT(CHAR(10),CONVERT(DATETIME,RTRIM(CONVERT(CHAR(2),MONTH(DATEADD(MM,1,DATEADD(YY,1,Date))))) + '/1/' +
RTRIM(CONVERT(CHAR(4),YEAR(DATEADD(MM,1,DATEADD(YY,1,Date)))))),101) as Start
FROM Tables..
where Start <= GetDate() but

but it says my column name start is invalid.
How should I go about doing this?

Thanks

homerjay80
Starting Member

26 Posts

Posted - 2007-05-03 : 18:10:14

Looks like I figured it out.

You have to repeat your alias expression in the where clause.

Select SSN ,
CONVERT(CHAR(10),CONVERT(DATETIME,RTRIM(CONVERT(CHAR(2),MONTH(DATEADD(MM,1,DATEADD(YY,1,Date))))) + '/1/' +
RTRIM(CONVERT(CHAR(4),YEAR(DATEADD(MM,1,DATEADD(YY,1,Date)))))),101) as Start
FROM Tables..
where
CONVERT(CHAR(10),CONVERT(DATETIME,RTRIM(CONVERT(CHAR(2),MONTH(DATEADD(MM,1,DATEADD(YY,1,Date))))) + '/1/' +
RTRIM(CONVERT(CHAR(4),YEAR(DATEADD(MM,1,DATEADD(YY,1,Date)))))),101) <= getdate()

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:45:57

wow that's a lot of converts.

Try this instead:
select dateadd(d, -Day(Date), dateadd(m, dateadd(yy, Date, 1), 1)) as Start
from yourTable

this shows the principle so play with this to get it right for your requirement.
Converts are slow and should be avoided.

Also to use the alias in the where you can use a subquery:

select SSN, Start
from (your upper query here) t1
where start <= getdate()

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 19:13:15

Using the Date() function here: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

you can also write

select dbo.Date(Year(somedate)+1, month(someDate)+1,1) as Start

which is pretty easy to read and work with.

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 19:14:32

i think i'll print those links to your functions jeff
and tape them to a wall

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources