Using GETDATE() to pull from a table

Question

My apologies in advance for the basic (and probably silly) question.

I'm doing a SELECT query where I'm joining several tables. I have a table that contains a numeric value associated with certain days. What I would like to have in my output is the numeric value attached to today, but I'm clueless as to how to make this happen. Obviously, I would have the same value for every record in my output and I'm fine with that.

Is this even possible? If so, what would an example of the code be?

The table from which I would like the numeric value simply has dates in one column ([Calendar_Day]) and integers in another column ([X_Value]). Below, you can see the last three lines of my SELECT statement. This latest attempt yielded NULL.

,[EnterpriseDB].[pcc].[conditions].Internal
,GETDATE() AS Date_Today 
,(SELECT [X_Value] FROM [Analytics].[WorkArea].[Days_Vals] WHERE [Calendar_Day] = GETDATE()) AS BizVal_Today

What does your table look like exactly? What have you tried? Please see [how to ask a good question](https://stackoverflow.com/help/how-to-ask). — Joe Farrell, Jul 16 '18 at 21:10
I've edited my comment, but doubt it will be much help. I obviously don't know what I'm doing. — The Kurgan, Jul 16 '18 at 21:37

sticky bit · Accepted Answer · 2018-07-16T21:52:50.167

Just guessing:

[Calendar_Day] is of the type date. While getdate() returns a datetime. That means the DBMS upcasts [Calendar_Day] to a datetime with a time of 00:00:00.0 in order to be able to compare the operands. But getdate() includes the current time. So unless you're executing the query at exactly 00:00:00.0 the values won't match and the subquery returns null.

To fix that, downcast getdate().

... WHERE [Calendar_Day] = convert(date, getdate()) ...

If [Calendar_Day] is also a datetime but you don't want to compare the hour (, minute, second ...) portion of it, downcast it as well.

... WHERE convert(date, [Calendar_Day]) = convert(date, getdate()) ...

Might throw any indexes out of the window though, if there are some on [Calendar_Day]. You might want to consider changing the data type to date or using a persistent, indexed computed column and compare against that, if that leads to any performance issues.

Using GETDATE() to pull from a table

1 Answers1