CODEWITHSUNDEEP
sqlsql-serversql-server-2012
tuhe
tuhe

Reputation: 23

SQL issue: cannot construct data type date some of the arguments have values which are not valid

I am working on an Excel project where I use SQL query to extract some data from SQL Server 2012.

When I select the data without filters, everything works fine.

However, when I use the DATEFORMATPARTS formula below, I get this error:

Cannot construct data type date some of the arguments have values which are not valid

A.[Invoice date] is the correct date format.

WHERE 
    A.[Customer] NOT IN ('100', '398', 399) 
    AND A.[Item] LIKE '1%'
    AND A.[Invoice date] >= DATEFROMPARTS(Year(DATEADD(yyyy, -1, GETDATE())), Month(DATEADD(yyyy, -1, GETDATE()) >) + 1, 1)

I've tried a lot of different stuff, but without luck.

Any guesses what is wrong in the above.

Thanks a lot in advance!

/ T

Upvotes: 1

Views: 5723

Answers (3)

tuhe
tuhe

Reputation: 23

Thanks for the input

it was precisely the month: 12 + 1

that was the issue! thanks

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

I believe > sign in the expression is a typo 

Month(DATEADD(yyyy,-1,GETDATE()))+1

This is resulting 13 which is a invalid month so you are getting that error.

Removing > and +1 from Month part will fix your issue 

select DATEFROMPARTS(Year(DATEADD(yyyy,-1,GETDATE())),Month(DATEADD(yyyy,-1,GETDATE())),1)

If you are trying to find last year last month first date then 

select datefromparts(year(getdate())-1,12,1)

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

You're going to have problems with that month calculation if you get a december and it adds a month to get month 13 of the year, this obviously isn't valid. If you want 11 months ago then do this;

DATEFROMPARTS(Year(DATEADD(YYYY,-1,GETDATE())),Month(DATEADD(mm,-11,GETDATE())),1)

Which (today, December 2016) returns '2015-01-01'

Upvotes: 0

Related Questions