CODEWITHSUNDEEP
sqlsql-serverdatabase
NeilSah
NeilSah

Reputation: 31

What this datediff check constraint on SQL table column is verifying?

Can anyone let me know what exactly date difference this below statement is verifying for column check constraint?

DATEDIFF(millisecond,[MyDateColumn],
         DATEADD(day,DATEDIFF(day,(0),[MyDateColumn]),(0)))=(0)))

Upvotes: 1

Views: 588

Answers (1)

Pred
Pred

Reputation: 9042

  • CAST(0 AS DATETIME) is 1900-01-01 00:00:00.000 (The default value of the DATETIME datatype). The integer 0 is implicitly converted to DATETIME.
  • datediff(day, (0), [MyDateColumn]) is the difference between the above date in days
  • dateadd(day,datediff(day,(0),[MyDateColumn]),(0)) is the midnight of the given day.
  • datediff(millisecond,[MyDateColumn],dateadd(day,datediff(day,(0),[MyDateColumn]),(0))) is the elapsed miliseconds since midnight to the given datetime value.

Basically it checks if the value in the column is at midnight. (A constraint to force the DATETIME data type to act as a DATE column.)

Upvotes: 1

Related Questions