CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldate
DtotheG
DtotheG

Reputation: 1257

Working out a date from a single month number SQL

Got 2 parameters @yr and @period, @period is just the month number so July would equal 7 for example.

In my stored procedure table I've got a column called Date which is just a standard datetime field. I need a where clause to work out all dates greater than the current period minus 1 year so if @period = 7 and @yr = 2012 I want the where clause to return all dates greater than '01-07-2011' (UK date format) how can I achieve this with just the 2 numbers from @period and @yr.

WHERE <br>
Date >= '01-07-2011'

Upvotes: 0

Views: 316

Answers (6)

David W
David W

Reputation: 10184

Just t make sure you compare against an entire date, one solution I'd offer is:

Select *
  from TheTable
 where date> DateAdd(Year,-1, convert(datetime, '01/'+convert(varchar,@period)+'/' + convert(varchar,@yr)))

To account for regional format differences in SQL Server 2012:

Select *
  from TheTable
 where date> DateAdd(Year,-1, DateFromParts(@year,@period,1))

For pre-2012:

Select *
  from TheTable
 Where Date > DateAdd(day, 0, DateAdd(month, @period-1, DateAdd(year, (@yr-1900)-1,0)))

The @yr-1900 is maintained to illustrate the computation of the base date offset from 1900, then subtracting 1 for the one-year-off date computation

Upvotes: 0

Sandeep Pulikonda
Sandeep Pulikonda

Reputation: 776

We can do it in may ways

try it 

DECLARE @a VARCHAR(20),
        @b VARCHAR(10),
        @c varchar(4)

SET @b='may'  /*pass your stored proc value */
SET @c='2011'

SET @a='01'+@b+@c
SET DATEFORMAT YDM
SELECT CAST(@a AS DATE)

FOR uk formate

SELECT CONVERT(char,CAST(@a AS DATE),103)

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

If you want the expression sargable, convert it to datetime:

declare @year int = 2012
declare @month int = 7

select 
   ...
where [Date] >= convert(datetime, convert(varchar(4), @year) 
                       + right('0' + convert (varchar(2), @month), 2) 
                       + '01')

After seeing Alex K.'s answer, you might even do this:

dateadd(month, @month - 1 + (@year-1900) * 12, 0)

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44336

For the best performance you should do something like this:

declare @yr int = 2012
declare @period int = 7

select ...
from ....
WHERE date >= dateadd(month, (@yr - 1901) * 12 + @period - 1, 0)

Upvotes: 1

Alex K.
Alex K.

Reputation: 175956

You could

Date >= dateadd(month, @period-1, dateadd(year, @yr-1900, 0))

Upvotes: 3

Chhatrapati Sharma
Chhatrapati Sharma

Reputation: 623

where year(date)>year(getdate()-1) and month(date)>@period

Upvotes: 2

Related Questions