datetime convertion

[Jupbs]

Hi,
I have two field in a table in SQL server 7. One is Month and another is year. Now i want to run a query which will select data less than say 06/20003. I did it in access like this...

WHERE CDATE(MMonth & '/' & MYear)< CDATE(06/2003)

I want to use same query in SQL server but it says Cdate is not a valid function so I used Convert function but now it says &quot;Conversion of Char datatype to datetime is resulting an out of range datatime value&quot;. My data type of both the field is integer. Please help me out.
Thank is advance.
jupbs

 

[ClaireHCM]

In the where condition put this


cast(cast(mm as varchar(2))+'/01/'+cast(yy as varchar(4)) as datetime) < '6/1/2003'

ex.select * from MYTable where cast(cast(mm as varchar(2))+'/01/'+cast(yy as varchar(4)) as datetime) < '6/1/2003'

 

[ClaireHCM]

If you want to include all the dates of 6/30 ,change '6/1/2003' to '7/1/2003'

 

[Jupbs]

ClaireHSU,
thank u so much. It works really well. But can i exclude the day part?
Jupbs

 

[ClaireHCM]

I am not sure.But I dont think so...Since the query I wrote use datetime to compare.It means the datetime part should be valid for datetime form.If you took the day park away.Then it wont be a valid form for datetime.Query analyzer will prompt you a syntax error for it.