5

I have month and year in separate columns, i want to take records between two points (ie, a start date and end date). I have month and year alone. One solution i have found is convert date,month combination into date .Say i have

Start month : 3 Start year : 2010
End month : 6 End year : 2013

I want to convert them as

 start date = 01-03-2010 

 End date = 31-06-2013

Any solutions?

bibinmatthew
  • 177
  • 2
  • 6
  • 13

6 Answers6

6

SqlServer 2012 onward supports DATEFROMPARTS(). It can be done this way :

    DECLARE @Month as int, @Year as int
    SET @Month = 8
    SET @Year = 2018
    SELECT DATEFROMPARTS(@Year,@Month,'01')

To get last date of month you can use EOMONTH() as below : 

    SELECT EOMONTH(DATEFROMPARTS(@Year,@Month,'01'))
Developer
  • 141
  • 2
  • 11
4

Following is the way to find month last and first date by passing month and year

DECLARE @month varchar(2)
DECLARE @year varchar(4)

First date of month

set @month='03'
set @year='2010'
select cast(@year + '-' + @month + '-01' as date)

output 

2010-03-01

Last date of month (Increment your month by 1 then subtract 1 day)

set @month='06'
set @year='2013'
select CAST(cast(@year + '-' + Cast(cast(@month as int )+1 as varchar(20)) + '-01' as datetime)-1 as DATE)

output 

2013-06-30
Siddique Mahsud
  • 1,453
  • 11
  • 21
2

Here is one method:

select convert(date, cast(startyear*10000 + startmon*100 + 1 as varchar(8)), 112)
. . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

As Marc B said, End month will be hard since you need to figure out what the last day of the specified year/month is.

Try this

DECLARE @StartMonth INT = 3 
DECLARE @StartYear INT = 2010 
DECLARE @EndYear INT = 2013 
DECLARE @EndMonth INT = 6 

SELECT Dateadd(year, @StartYear - 2000,Dateadd(month, @StartMonth - 1, '20000101')) AS StartDate, 
       Dateadd(year, @EndYear - 2000, Dateadd(month, @EndMonth - 1, '20000101')) AS EndDate
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

If the dateparts are stored as ints you could do something simply like cast them to varchar, concatenate, and cast again. For example:

DECLARE @StartYear INT = 2012
DECLARE @StartMOnth INT = 6

SELECT CAST(CAST(@StartYear AS CHAR(4)) + '-' + CAST(@StartMonth AS VARCHAR(2)) + '-01' AS DATE)

On the end date you can use a CASE statement to identify the last day of the month. And don't forget leap years.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
0

You can use sql function

Select cast(('01-'+h.[monthyear]) as date) from table1 h
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Ambreen Haleem
  • 26
  • 2