CODEWITHSUNDEEP
sqlsql-servert-sql
atypical
atypical

Reputation: 1100

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

I get this error on following code; Msg 289, Level 16, State 3, Line 2 Cannot construct data type datetime, some of the arguments have values which are not valid.

But i think values are valid.

MERGE INTO tblAveraj WITH (HOLDLOCK) 
USING tblDegerSon ON 
  (tblAveraj.AdresID = tblDegerSon.AdresID 
    AND tblAveraj.Baslangic = DATETIMEFROMPARTS(2014, 11, 11, 11, 0, 0, 0))
WHEN MATCHED 
     THEN 
         UPDATE 
            SET tblAveraj.Toplam = tblAveraj.Toplam + tblDegerSon.Deger, 
                tblAveraj.Sure = tblAveraj.Sure + 1
WHEN NOT MATCHED 
     THEN 
         INSERT (AdresID, Baslangic, Bitis, Toplam, Sure) 
         VALUES (
                   tblDegerSon.AdresID, 
                   DATETIMEFROMPARTS(DATEPART(YEAR, GETDATE()), 
                                      DATEPART(MONTH, GETDATE()), 
                                      DATEPART(DAY, GETDATE()), 
                                      DATEPART(HOUR, GETDATE()), 0, 0, 0), 
                   DATETIMEFROMPARTS(DATEPART(YEAR, GETDATE()), 
                                     DATEPART(MONTH, GETDATE()), 
                                     DATEPART(DAY, GETDATE()), 
                                     DATEPART(HOUR, GETDATE()) + 1, 0, 0, 0), 
                   Deger, 1);

Upvotes: 2

Views: 23840

Answers (2)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

These type of error while using DATETIMEFROMPARTS

If I passed an invalid argument to DATETIMEFROMPARTS ex(day cannot be 32 in a month & hour cannot be 25 in a day) to above functions. So the constructor of this function cannot create a date from these arguments, so it generates the error.

Whenever you come across this error, please check the arguments passed in the function. The arguments must be valid to avoid this error.

The short and sweet summary is that the DATETIMEFROMPARTS function takes valid integers as input for year, month,date and hour. It returns a date variable representing the parts. Any NULL input values result in NULL output values. Last but not least, any invalid input results in a ERROR.

Upvotes: 4

jpw
jpw

Reputation: 44871

the only reason I can see is that the very last DATETIMEFROMPARTS in the INSERT part for the column Bitis (if I read it correctly):

DATEPART(HOUR, GETDATE()) + 1

overflows when the hour + 1 gets above 24.

Doing:

SELECT DATETIMEFROMPARTS(DATEPART(YEAR, GETDATE()), DATEPART(MONTH, GETDATE()), DATEPART(DAY, GETDATE()), DATEPART(HOUR, GETDATE()) + 2, 0, 0, 0)

right now (my local time is 2014-11-23 22:53:49.383) gives me the same error.

The quick fix would be to do a case when DATEPART(HOUR, GETDATE())+1 > 24 then 0 end or something similar.

Upvotes: 3

Related Questions