Date conversion

[CrysUser]

Hi,
In order to retreive all the records with created datetime in last month here is the SQL I use,

Select * from table a where
a.created_dt >= Convert(datetime, convert(char, month(getdate()-day(getdate())))+'/01/'+
convert(char,year(getdate()-day(getdate()))))
and a.created_dt < Convert(datetime, convert(char, month(getdate()))+'/01/'+convert(char,year(getdate())))

[Note: a.created_dt column is a datetime field]

My question is,
IS there any other way to calculate the begin and end of last month. This convert formula gets the query to respond a lot slower compared to substituting the actual date values.

Any help would be greatly appreciated.

 

[bdreed35]

Try this:

Select *
FROM Table a
WHERE CONVERT(char(10),a.created_dt,101) >= 
      CONVERT(char(10),DateAdd(month,-1,DateAdd(day,(-Day(GetDate())+1),GetDate())),101)
AND   CONVERT(char(10),a.created_dt,101) <
      CONVERT(char(10),DateAdd(day,-Day(GetDate()),GetDate()),101)

You could also try the Month and Year functions.

Select *
FROM Table a
WHERE Year(a.created_dt) = Year(DateAdd(day,-Day(GetDate()),GetDate()))
AND Month(a.created_dt) = Month(DateAdd(day,-Day(GetDate()),GetDate()))

~Brian

 

[CrysUser]

I will try this.
Thank you for the suggestions.