# question

## 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

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

·
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.

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

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

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.