Solved

# Date Question

Posted on 2007-11-26
Medium Priority
242 Views
I have a query looking at yesterdays date when run, as shown in the query below.

I want to add last full month as a datechoice, i.e.
f @datechoice = 'Last Full Month'
begin

I need help settin the last full month for @stat and @start1, I will need an @end and @end1 too as well.

Any help would be appreciated

Thanks

declare @datechoice varchar(20)

declare @start varchar(20)
declare @start1 datetime

if @datechoice = 'Yesterday'
begin
set @start = CONVERT(varchar(20), DATEADD(dd, - 1, GETDATE()), 103)
set @start1 = CONVERT(char(10), GETDATE() - 1, 101)
end

SELECT
FROM
WHERE     (CONVERT(varchar(10), field1 112)) = @start

UNION ALL

SELECT
FROM
WHERE      CONVERT(char(10), field1, 101) = @start1
0
Question by:halifaxman
• 2
• 2

LVL 39

Expert Comment

ID: 20349081
set @start = CONVERT(varchar(20), DATEADD(month, - 1, GETDATE()), 103)
set @start1 = CONVERT(char(10), GETDATE() - 1, 101)
0

LVL 39

Expert Comment

ID: 20349094
if month is not working , try mm or m
0

LVL 5

Expert Comment

ID: 20349123
Both MM or month will work with DATEADD function.
0

Author Comment

ID: 20356599
Hi,

By Last Full Month, I meant that if I ran it today @start and @start1 would equal 1st October and @end and @end1 would equal 31st October, and if I ran it in December the dates would be in November and so on

Cheers
0

LVL 5

Accepted Solution

ursangel earned 1000 total points
ID: 20381154
declare @TempStart datetime
set @TempStart = ltrim(datepart(month, GETDATE())) + '/01/' + ltrim(datepart(Year, GETDATE()))
set @start = CONVERT(varchar(20), dateadd(month, -1, @TempStart))
set @start1 = CONVERT(char(10), @TempStart - 1, 101)

similarly do it for @end
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.