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

Question

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

You have a > sign in the expression.

Gordon Linoff
– Gordon Linoff

2016-12-20 16:02:37 +00:00
Commented Dec 20, 2016 at 16:02 — Gordon Linoff
– Gordon Linoff, Commented Dec 20, 2016 at 16:02

tuhe · Accepted Answer · 2016-12-21 13:16:55Z

1

Thanks for the input

it was precisely the month: 12 + 1

that was the issue! thanks

answered Dec 21, 2016 at 13:16

tuhe

231 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cryptc Over a year ago

You can use MONTH(12 + 1), or DATEADD(MONTH, 1, 12). But be careful when using DATEFROMPARTS, since 12+1 also increases the year by +1.

Rich Benner · Accepted Answer · 2016-12-20 16:06:41Z

0

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'

answered Dec 20, 2016 at 16:06

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Comments

Pரதீப் · Accepted Answer · 2016-12-20 16:11:37Z

0

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)

edited Dec 20, 2016 at 16:11

answered Dec 20, 2016 at 16:03

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related