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
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.
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)
@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'
19 People are following this question.
