SQLTeam.com | Weblogs | Forums

Stored Procedure to Update Dim Date table to reflect new day

I know how to create and insert a Dim Date table into my SSMS database.
However, I want to then update this table every night at 00:01 hours to reflect the new day.
let me keep it simple.
Let's say I have two columns only. One with the date in yyymmdd format and then another column which has a 1 if it is the current day and 0 otherwise.
Obviously, this second column, let's call it "IsCurrentDay", needs to be updated every day to reflect the new day.

Can anyone please me regarding this?


Instead of updating the table - add a computed column to the table. For that computed column you can use a simple check. Assuming the date column is a DATE data type:

CASE WHEN datecolumn = CAST(getdate() AS DATE) THEN 1 ELSE 0 END

With that said - I don't see any value to having this extra column available in the table. In all cases, it is just as easy to include the check in the query as it would be to query this additional column.