question

aboulhad avatar image
aboulhad asked

How to compare a Date Column with today date

The following formula is not working with me WHEN DAY(GETDATE()) > [VALUE DATE MM/DD/YYYY] THEN 'CONFIRM' Any idea what im doing wrong
sql serverdate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HAD avatar image
HAD answered
Is [VALUE DATE MM/DD/YYYY] an integer? If not that is probably the case since Day function returns an integer of the day of the month.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered
good thought HAD, so aboulhad, are you trying to get the midnight point of getdate()? if so, use datediff() and dateadd() like so: SELECT dateadd(dd,datediff(dd,'1/1/1900',getdate()),'1/1/1900') (basically, find the number of days between a date (in this case '1/1/1900') and today, then add that number to '1/1/1900' to get the datetime at that point in time, which is midnight since not specified)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@aboulhad It appears that you have a column of the date or datetime data type (with time value dropped). If this is the case then both answers above are correct: You cannot possible use the Day function because it is useless for your concern. It simply returns the day number of the month, that is all. It cannot possibly be used here. If you need to come up with the way to drop the time part from today's date then as @Jon Crawford already pointed out, there is no better way than the dateadd / datediff combination, i.e. select dateadd(day, datediff(day, 0, getdate()), 0) TodayAtMidnight For those who don't like/ don't want to understand how and why it works there is also a cheesy way: select cast(getdate() as date) TodayAtMidnight Even though the line above returns date rather than datetime type, you can still compare the two. To summarize: just restate the part of your script which does not work to read this: when dateadd(day, datediff(day, 0, getdate()), 0) > [VALUE DATE MM/DD/YYYY] then 'Confirm'
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.