DATEFROMPARTS and CONVERT failing but only when inside an SP

Question

DATEFROMPARTS and CONVERT failing but only when inside an SP

Thom A

SSC Guru

Points: 99133
More actions
February 6, 2020 at 10:35 am

#3723540
OK, I personally hate this question, as no matter what I've tried, I have been completely unable to repro this issue outside of the SP that is causing the problem. I really apologise for that.
Anyway, the problem I'm having is that I have an SP, which also calls somefunctions, one of which uses DATEFROMPARTS. This is a process that I deployed last Thursday, and was working fine; till this morning. When the automated process ran this morning it generated the error:
Cannot construct data type date, some of the arguments have values which are not valid.
Ok, I recognise that error, that means that DATEFROMPARTS is getting some bad dates (for example SELECT DATEFROMPARTS(2019,13,7); would cause the error). Good, but all the DATEFROMPARTS in the SP are in the format DATEFROMPARTS(Column,1,1) and all the columns have a constraint meaning that it must be positive or be NULL; so they would all be valid.
So I go to the SP, comment out the ALTER line, and run the SQL underneath with the same parameter. The entire statement runs without an error. Ok...? So instead I ran the SP with the parameter. I got the error above. Wah?
Fine, maybe, it's some kind of odd Parameter Sniffing issue causing the problem. I ALTER the SPs, and rerun the EXEC. Same error. I ALTER the SP changing the code a little (nothing that would actually really change it, but move the order of items in the FROM and WHERE). SQL runs fine again, EXEC fails. Fine... I DROP the SP and CREATE it again. Still the same error when I use EXEC.
As a test, I therefore ran the SP with yesterday's parameters (the data for yesterday should not be different). Failed. Day before, failed. You get the idea. All attempts failed for processes that had succeeded the days before.
Really lost at this point, so, just in case, I restart the instance (It's production, so not ideal, however, it's a report we legally need to supply that's failing, so needs must). This was just in case something like an update or something had caused the problem. Few minutes later, try again. Nope, this errored. ARGH! >_<
Right, a different plan of attack instead. I replace all uses of DATEFROMPARTS(Column,1,1) with CONVERT(date,CONCAT(Column,'01','01')), run the SQL, it works (like it always has). Deploy the change, EXEC the SP and surprise!
Conversion failed when converting date and/or time from character string.
Seriously?
I solved the problem by using TRY_CONVERT instead of CONVERT, however, there are no NULLs in the column when I EXEC the SP; meaning none failed to convert. I therefore have no idea how or why this is failing. Yes, I've fixed the report, but I really want to understand why SQL Server suddenly fails. The fact that the SQL itself works, but the SP fails really makes no sense, especially with the same parameter.
The below doesn't repro the issue, but I effectively have a set up like this:
```
CREATE FUNCTION dbo.GetSomeDate (@i int)
RETURNS table
AS RETURN

    SELECT DATEFROMPARTS(@i,1,1) AS DFP;
GO

CREATE PROC dbo.NoRepro AS

    SELECT *
    FROM (VALUES(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020))V(Y)
         CROSS APPLY dbo.GetSomeDate(V.Y);
GO
```
If you were to run EXEC dbo.NoRepro;, then you would get the errors above. If, however, you ran the SQL inside the SP, then it would succeed.
If anyone does have any ideas, would be great; I'm at a loss. Yeah, the problem is solved, but I didn't ever find out why it's erroring, and it still will if I revert the change, so something is wrong. I just don't know what.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Thom

Are you sure you don't have any five-figure years? That would explain the DATEFROMPARTS and CONVERT failures, although not why TRY_CONVERT returned no NULLs.

SELECT DATEFROMPARTS(10000,1,1);
GO
SELECT CONVERT(date,CONCAT(10000,'01','01'));
GO
SELECT TRY_CONVERT(date,CONCAT(10000,'01','01'));
GO

For what it's worth, I'd do it something like this. I haven't tested it, but I expect that simple date arithmetic would perform better.

SELECT DATEADD(YEAR,Column-1999,'19990101');

John

Thom A SSC Guru Points: 99133 More actions · Answer 2

John Mitchell-245523 wrote:

Thom
Are you sure you don't have any five-figure years? That would explain the DATEFROMPARTS and CONVERT failures, although not why TRY_CONVERT returned no NULLs.

The constraint stops values that large too, forgot to mention. I did, however, check just in case, but no rows. Like you said, that doesn't explain why there's no NULL values either, nor does it explain why the SP fails, but the SQL itself does not.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

American date format issues ? - your original code block had DATEFROMPARTS(2019,13,7)

usually when I see 13 in any date function then I fear it is trying to do the 7th of the currently undiscovered month between December and January.

is it worth setting a Dateformat YYDDMM type operation in the proc.

is it possible that there is different security context the proc is executed under that is in british English ?

MVDBA

Thom A SSC Guru Points: 99133 More actions · Answer 4

Hi Mike,

That isn't how DATEFROMPARTS works. It's DATEFROMPARTS(<year>,<month>,<day>) regardless of the language. The block you quote is me describing when they error usually occurs; but that could never happen with both <month> and <day> are both hard coded to 1.

Either way, I always use yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn when writing SQL.

is it possible that there is different security context the proc is executed under that is in british English ?

Yes, they were, all accounts are set to British English on the Server, which the exception of one poorly written third party application.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

Thom A wrote:

YYY-MM-ddThh:mm:ss.nnnnnnn

Good DBA you should see the number of times an app on some estates breaks on the 13th of the month - usually not figured out until valentines day (14th) and my date gets cancelled

MVDBA

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

If you were to run EXEC dbo.NoRepro;, then you would get the errors above. If, however, you ran the SQL inside the SP, then it would succeed.

No errors for me:

2020-02-06_08-34-48

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

is ti sql 2019? we've seen a few issues with functions not behaving as expected - not honouring order by... is it possible that this is another sql 2019 bug?

if you are on 2019 - set the compatability mode to 2016 and see if the proc still misbehaves, if it does then mr Microsoft needs a call

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

Phil Parkin wrote:

If you were to run EXEC dbo.NoRepro;, then you would get the errors above. If, however, you ran the SQL inside the SP, then it would succeed.
No errors for me:

no errors for me in 2019 in any compatibility mode

MVDBA

Thom A SSC Guru Points: 99133 More actions · Answer 9

Phil Parkin wrote:

If you were to run EXEC dbo.NoRepro;, then you would get the errors above. If, however, you ran the SQL inside the SP, then it would succeed.
No errors for me:

No I've not been able to repro the issue in a Sandbox (what I say at the start of OP); that's one of the reason why I hated asking this, as I can't post a way to show you apart from obviously getting you to look at the live environment (which obvious I can't do). Why I called the object "NoRepro" 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99133 More actions · Answer 10

MVDBA (Mike Vessey) wrote:

is ti sql 2019? we've seen a few issues with functions not behaving as expected - not honouring order by... is it possible that this is another sql 2019 bug?

No we're still on 2012 here at the moment. Upgrade is due some time this year, if i can get the budget.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246979 More actions · Answer 11

No I've not been able to repro the issue in a Sandbox (what I say at the start of OP); that's one of the reason why I hated asking this, as I can't post a way to show you apart from obviously getting you to look at the live environment (which obvious I can't do). Why I called the object "NoRepro"

Apologies, I misunderstood.

Maybe you should have written: "If you were to run EXEC dbo.NoRepro in my environment ..." 🙂

This reply was modified 4 years, 10 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 12

Phil Parkin wrote:

Maybe you should have written: "If you were to run EXEC dbo.NoRepro in my environment ..." 🙂

dbo.NoRepor won't even do it in my environment though >_< It's literally only the production SP that does it. I'm happy to post the SP (and related function), but I wouldn't be able to provide sample data for it; which kind of makes it useless.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 13

I'm stretching here on this one (and i'll get shot down) - if it works on one server but not on another then it's either a database or server setting.

if you have a brand new empty database on the server that it doesn't work on and try and run it... same error??? that indicates a server issue or a login language issue

if it works in a new clean database then we are looking at a database configuration issue, either default language or collation...

personally i'm stumped

MVDBA

Phil Parkin SSC Guru Points: 246979 More actions · Answer 14

Can we assume that you have a QA/Test environment which is intended to be a close-to exact copy of Prod? If so, does the problem happen there too?

If not, I'd start digging into instance-level settings and comparing them. I've seen scripts before which can grab lots of this stuff for you, though I don't have one to hand.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel