converting a datetime to string in SQL

[cards4]

Hello,
I am running a SQL query that grabs information from the previous month. For example, on August 1, I plan to run this query to get information I need for the month of July.

"WHERE (((act_task.billable_time) Is Not Null Or (act_task.billable_time)>0) AND ((act_task.status_dt) Between
dateadd(month, -1, getdate()) + '-01' And dateadd(day, -1, getdate())))"

This is the where clause that I am using and I'm using a between function to get the first day of the previous month and the last day of the previous month. This where clause is giving me a lot of datetime errors. I want to convert this datetime into a string to satisfy the between function. Please help! Thank you.

 

[gmmastros]

Instead of doing string manipulations, you are better off using data calculations.

And DateDiff(Month, 0, act_task.status_dt) = DateDiff(Month, 0, GetDate()) - 1

DateDiff will tell you the difference in 2 dates in whatever units you choose (in this case month). The first date used in the calculations is 0 (which is Jan 1 1900).

For example:

Select DateDiff(Month, 0, '08/01/2006'), DateDiff(Month, 0, '07/10/2006')

I also recommend you look at this thread: thread183-1240616
and this FAQ's: faq183-5842

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom