CODEWITHSUNDEEP
sqlsql-server-2012
sd_dracula
sd_dracula

Reputation: 3896

sql datefromparts not working as expected

I have the following code:

Declare @indDate date = '1/1/2014'

SELECT  RecordID AS RID,
            Name, 
            Company, 
            PhoneNumber, 
            CONVERT(VARCHAR(10),DateOfInduction,103) AS StartDate,
            CONVERT(VARCHAR(10),DateOfExpiry,103) AS DateOfExpiry
    FROM Records WHERE DateOfExpiry <= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,@indDate),DATEPART(DD,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@indDate)+1,0))))
    AND DateOfExpiry >= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,@indDate),1)

Which basically gives me all expiring records for the month of the @indDate parameter.

What I am trying to do is add 1 to the month in the DATEFROMPARTS to get next month's values. Now I don't want to just change the @indDate to the other month because in the report the date will not be selected but only something like Expires This Month, Next Month, Previous Month, etc.

So if I change the WHERE clause to: 

WHERE DateOfExpiry <= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,DATEADD(MM,1,@indDate)),DATEPART(DD,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@indDate)+1,0))))
    AND DateOfExpiry >= DATEFROMPARTS(DATEPART(YYYY,@indDate),DATEPART(MM,DATEADD(MM,1,@indDate)),1)

I get error: Cannot construct data type date, some of the arguments have values which are not valid. and I am not sure why, it works fine If I try something like:

Declare @indDate date = '1/1/2014'
select DATEADD(mm,1,@indDate)

Can anyone see the problem?

Upvotes: 0

Views: 2159

Answers (2)

user275683
user275683

Reputation:

In your error is coming from trying to create date from invalid date range,

if you run parts of your DATEFROMPARTS in the following manner

DECLARE @indDate DATE = '1/1/2014'
SELECT DATEPART(YYYY, @indDate)
       ,DATEPART(MM, DATEADD(MM, 1, @indDate))
       ,DATEPART(DD, DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @indDate) + 1, 0)))

this generates 2014 2 31 which is not a valid date.

The following three examples will give you all records for previous, current or next month no matter if you passed in '01/01/2014' or '01/15/2014'

This will give you all records for last month.

WHERE DateOfExpiry >= DATEADD(MONTH, -1,DATEADD(dd,-DATEPART(day,@indDate)+1,@indDate)) AND
DateOfExpiry < DATEADD(dd,-DATEPART(day,@indDate)+1,@indDate)

This will give you all records for current month

WHERE DateOfExpiry >= DATEADD(dd,-DATEPART(day,@indDate)+1,@indDate) 
and DateOfExpiry < DATEADD(MONTH, 1,DATEADD(dd,-DATEPART(day,@indDate)+1,@indDate))

Finally all records for next month

WHERE DateOfExpiry >=  DATEADD(MONTH, 1, DATEADD(dd, -DATEPART(day, @indDate) + 1, @indDate))
AND DateOfExpiry < DATEADD(MONTH, 2, DATEADD(dd, -DATEPART(day, @indDate) + 1, @indDate))

Upvotes: 2

KM.
KM.

Reputation: 103667

why not use: 

WHERE DateOfExpiry >= @indDate AND DateOfExpiry < DATEADD(month,1,@indDate)

Upvotes: 0

Related Questions