[SQL] Arithmetic overflow error datetime

[PdeBie]

hoi allemaal,

Bij het vergelijken van 2 datums middels een datediff, krijg ik de volgende error:
Arithmetic overflow error converting expression to data type datetime.

Na wat google werk, ben ik erachter gekomen dat dit te maken heeft met het feit dat de Culture van de datum dit probleem veroorzaakt. Als we kijken naar de datum van vandaag '06-09-2011' is het voor de server niet duidelijk of het nu 6 september of 9 juni is.

Ik dacht dit probleem te verhelpen met een CONVERT, door de twee te vergelijken datums naar hetzelfde format te converteren.
hiervoor gebruikte ik:

SQL:

--input voorbeeld: 2011-09-06 09:40:30.630

CONVERT(VARCHAR(23), '2011-09-06 09:40:30.630', 121)
-- resultaat: 2011-09-06 09:40:30.630

--vergelijken met
CONVERT(VARCHAR(23), GETDATE(), 121)
-- resultaat: 2011-09-06 11:13:50.550

de resultaten zijn hetzelfde format, dus dit zou goed moeten zijn.
Echter krijg ik nog steeds de foutmelding.
Iemand een idee?

[ Voor 8% gewijzigd door PdeBie op 06-09-2011 11:13 . Reden: aanvullende info ]

[RobIII]

De datums staan als varchar in de DB? Of als DateTime(2) ?

[PdeBie]

als datetime.

ik heb net m'n begin post aangepast met wat aanvullende informatie trouwens.
even wat resultaten geplaatst.

De datediff die ik uit wil voeren is als volgt:

SQL:

DATEDIFF(minute, CONVERT(VARCHAR(23), '2011-09-06 09:40:30.630', 121), CONVERT(VARCHAR(23), GETDATE(), 121)) 

[RobIII]

pdebie schreef op dinsdag 06 september 2011 @ 11:16:
als datetime.

En wat zijn 2 werkelijke values die je in datediff mikkert? Dus in Datediff(minute, A, B ), wat zijn 2 (voorbeeld) waardes van A en B?
Want als je values als DateTime in de tabel staan (en je doet een datediff met getdate() ) dan zie ik niet waarom dit een arithmetic overflow zou veroorzaken anders dan dat de uitkomst van de datediff niet in 't gewenste resulttype zou passen (zie return value).

Ik zie dan ook even niet waarom je nog zou gaan lopen rommelen met convert etc. als de datums als DateTime in de tabel staan. De culture waar je naar refereert is alleen interessant wanneer je varchars gebruikt (of in queries, waar een value als '02-06-2009' omgezet zal moeten worden naar 2 juni danwel 6 februari). Zie ook onze Getallen en talstelsels FAQ (puntje 9-11)

[ Voor 37% gewijzigd door RobIII op 06-09-2011 11:29 ]

[PdeBie]

dat voorbeeld wat ik gaf (zie hieronder) is een daadwerkelijke value die ik als input gebruik.

SQL:

'2011-09-06 09:40:30.630'

Maar nu ik er wat langer naar kijk, krijg ik het vermoeden dat het een niveau hoger ligt en niet (meer) in de DATEDIFF functie. Want als ik de DATEDIFF in een losse query analyzer draai, werkt hij inderdaad naar behoren.

Ik gebruik de DATEDIFF in een WHERE-clause. De DATEDIFF moet 5 of groter zijn om true te zijn.
Alleen vraag ik me af wat hier nog fout in kan gaan.

RobIII schreef op dinsdag 06 september 2011 @ 11:21:
[...]

Ik zie dan ook even niet waarom je nog zou gaan lopen rommelen met convert etc. als de datums als DateTime in de tabel staan. De culture waar je naar refereert is alleen interessant wanneer je varchars gebruikt

Was het eerste wat ik tegenkwam op Google, dus proberen hè

Dacht dat GETDATE() en mijn input misschien qua format verschilde. En daarom dat ik deze gelijk wilde trekken.

[ Voor 32% gewijzigd door PdeBie op 06-09-2011 11:32 . Reden: toevoeging argumentatie ]

[RobIII]

pdebie schreef op dinsdag 06 september 2011 @ 11:29:
Was het eerste wat ik tegenkwam op Google, dus proberen hè

Ah, dat doe ik ook altijd als ik in de winkel mijn erwtjes niet kan vinden; dan mikker ik gewoon de rest uit de schappen in mijn wagentje en bid dat er iets tussen zit dat ik lust

Hoe ziet je query eruit wanneer 'ie naar je DB wordt gegooid? Je hebt 't over "een niveau hoger"; wat gebeurt daar precies?

pdebie schreef op dinsdag 06 september 2011 @ 11:29:

Dacht dat GETDATE() en mijn input misschien qua format verschilde. En daarom dat ik deze gelijk wilde trekken.

Er is geen formaat (wanneer je values écht als datetime in tabel staan); dat is nou net de clou. GetDate() returned (ook) een DateTime. Een formaat is enkel en alleen van toepassing in de presentatielaag (je applicatie's UI, of SQL Server Management Studio of...). Twee DateTimes kun je dus straffeloos comparen/diffen/whatever zonder rekening te houden met "formaten"; dat is iets dat enkel presentatielagen interessant vinden. (Je moet wél rekening houden met timezones etc dus alles (bijv.) in UTC opslaan, maar dat is een ander verhaal...)

[ Voor 45% gewijzigd door RobIII op 06-09-2011 11:35 ]

[Redshark]

pdebie schreef op dinsdag 06 september 2011 @ 11:11:
Bij het vergelijken van 2 datums middels een datediff, krijg ik de volgende error:
Arithmetic overflow error converting expression to data type datetime.

Waarom ga je het datatype aanpassen als je toch al je data in datetime hebt staan? Is overbodig lijkt.

De fout ligt idd niet in je datadiff functie, maar in het converten van een stukje data, dat zegt de foutmelding ook nml:
error converting expression

[PdeBie]

RobIII schreef op dinsdag 06 september 2011 @ 11:33:
[...]

Ah, dat doe ik ook altijd als ik in de winkel mijn erwtjes niet kan vinden; dan mikker ik gewoon de rest uit de schappen in mijn wagentje en bid dat er iets tussen zit dat ik lust

haha, ik heb m'n beargumentatie in m'n vorige post wat duidelijker gemaakt.
Misschien dat je dan m'n keuze snapt. Alleen het Google argument was niet het enige.
Klinkt al logischer nu of niet?

Maar wat ik doe. Ik heb een SELECT statement welke records selecteert op basis van een WHERE clause, waar deze datediff dus onder valt.

Deze query ziet er als volgt uit:
de tabel #Retour is een temp tabel, die ik voor een UPDATE statement gebruik welke de records uit onderstaande query update

SQL:

SELECT ot.OrderId as OrderId, 
       ot.[OrderStatus], 
       ot.Sender, 
       MAX(ot.DateChanged) AS DateChanged
INTO #Retour
FROM OrderTrack ot INNER JOIN OrderHead oh ON ot.OrderId = oh.ID
WHERE (ot.Sender <> ot.Receiver) AND (ot.[OrderStatus] = 1) AND (oh.[Status] = 1)
    AND (DATEDIFF(minute, (select MAX(OrderTrack.DateChanged)
                            FROM OrderTrack INNER JOIN OrderHead ON OrderTrack.OrderId = OrderHead.ID
                            WHERE (OrderTrack.Sender <> OrderTrack.Receiver) AND (OrderTrack.[OrderStatus] = 1) AND (OrderHead.[Status] = 1)
                            AND Ordertrack.OrderId = ot.OrderId
                            ), GETDATE()) > 5)
Group by ot.OrderId, ot.OrderStatus, ot.Sender

De converts zijn hier uit de query gehaald en de melding krijg ik nog steeds.

Redshark schreef op dinsdag 06 september 2011 @ 11:35:
[...]

Waarom ga je het datatype aanpassen als je toch al je data in datetime hebt staan? Is overbodig lijkt.

Dacht dat GETDATE() en mijn input misschien qua format verschilde. En daarom dat ik deze gelijk wilde trekken.

[ Voor 12% gewijzigd door PdeBie op 06-09-2011 11:44 ]

[RobIII]

pdebie schreef op dinsdag 06 september 2011 @ 11:41:
[...]


haha, ik heb m'n beargumentatie in m'n vorige post wat duidelijker gemaakt.

Daar heb ik ook op gereageerd
RobIII in "[SQL] Arithmetic overflow error datetime"

[PdeBie]

ok helemaal duidelijk.

heb het probleem inmiddels ook ontdekt.
Het ging inderdaad niet fout in het select statement, maar in de opvolgende UPDATE query (die ik in mijn post hierboven heb vermeld waar de SELECT voor dient)
Daar ging iets fout met de datum.

Stomme is, is dat het message scherm van de query analyser een totaal ander regelnummer aangeeft waar het fout gaat.

[Moraelyn]

Ik zie maar zelden bij mijn eigen fouten het juiste regelnummer staan. Daar kijk ik ook niet meer naar.

[RobIII]

^ Waar je rekening mee moet houden is dat veel editors/IDE's aan wordwrap doen en dan regels soms anders tellen en vaak wordt een regelnummer vanaf 't begin van een query geteld; als je meerdere queries in je editor/IDE hebt staan komt dat regelnummer dus niet overeen met het regelnummer van de query waar 't mis gaat.