Solved

Date Question

Posted on 2007-11-26
5
235 Views
Last Modified: 2010-03-20
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
Comment
Question by:halifaxman
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
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

by:Pratima Pharande
ID: 20349094
if month is not working , try mm or m
0
 
LVL 5

Expert Comment

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

Author Comment

by:halifaxman
ID: 20356599
Hi,

Thanks for the answers.

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

by:
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question