CODEWITHSUNDEEP
sql-server-2012
Lakhae
Lakhae

Reputation: 1899

How to get full date from the given month and year in SQL SERVER?

I have following script where I need to get the full date 

DECLARE @BeginDate DateTime
DECLARE @EndDate DateTime
Declare @month int
Declare @year int 
set @month = 6
set @year = 2014

Select beginDate = CAST(@month AS VARCHAR(10)) + '/01/' + CAST(@year AS VARCHAR(10))
SELECT endDate = DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @beginDate) + 1, 0))

This script returns endDate as null. How do I get full endDate by passing only Date (not time)?

Upvotes: 0

Views: 68

Answers (2)

Zafer
Zafer

Reputation: 2190

I think you forgot to put @ sign in front of the variables. If you change the last two lines with the following:

Select @beginDate = CAST(@month AS VARCHAR(10)) + '/01/' + CAST(@year AS VARCHAR(10))
SELECT @endDate = DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @beginDate) + 1, 0))

it should work. By putting "select @enddate" at the end, I got 2014-06-30 00:00:00.000 .

Upvotes: 1

Lakhae
Lakhae

Reputation: 1899

Well I found it.

DECLARE @BeginDate DateTime
DECLARE @EndDate DateTime
Declare @month int
Declare @year int 
set @month = 7
set @year = 2014

Select @beginDate = CAST(@month AS VARCHAR(10)) + '/01/' + CAST(@year AS VARCHAR(10))
SELECT endDate = DATEADD(d, -DAY(DATEADD(mm, 1, @beginDate)), DATEADD(m, 1, @BeginDate))

source

Upvotes: 0

Related Questions