Solved

# Date Question

Posted on 2007-11-26
233 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 250 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.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

hyperlink data type in SQL 3 28
Passing Parameter to Stored Procedure 4 24
SSRS Enable Remote Errors 4 26
divide by zero error 23 16
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.