Dates in SQL
One piece of data that is commonly required in SQL, or any programming for that matter, is the current date and time.
In SQL, the ones that I use the most are getdate(), and getutcdate(), to get the date and time information. These return the current date and time for the local time, and UTC (formerly GMT), respectively. They are returned in the SQL datetime format.
So, the simple statement:
SELECT getdate()
will return the current date and time for the local timezone. If you need to deal with servers in different time zones, and order of events in real time is important, then you should use getutcdate(). That way all of your servers will be using the same baseline.
While being able to get the current date and time is useful, many times what is desired is be able to find a relative date, such as the date a week ago. This is what is required to get the sales for the past week, for example.
To get the date a week ago, using DATEADD, you simply subtract 7 days. DATEADD takes three argument:
1) The "datepart", year, day, etc. In this case "day"
2) The number you want to adjust it by. In this case seven days ago, so -7
3) The date to base it on. In this case the current date, or getdate()
Putting it together, the statement:
SELECT DATEADD(day,-7,getdate())
will get you the date and time, exactly seven days ago. So, if it is currently 2:15 PM on 12/15/2012, this statement will return "12/8/2012 2:15:00 PM".
This is close to what we need if we are looking for the date a week ago, but it includes the time, which we may not want. There are a few ways to deal with removing the time. One that I use frequently, is the CONVERT function. Using this, we can convert the date and time returned, into a VARCHAR that contains only the date.
The CONVERT function take three arguments, also:
1) Data type. In this case VARCHAR
2) Expression (what to convert). In this case the DATEADD from above
3) Style. In this case, style 101 converts a date and time to MM/DD/YYY format
Adding the CONVERT into the statement, give us:
SELECT CONVERT(VARCHAR,DATEADD(day,-7,getdate()),101)
If this was run at any time on 12/15/2012, the result would be 12/8/2012, which is the date seven days prior.
The DATEADD and CONVERT functions have many more options that are beyond the scope of this blog. I will look at them in more detail in a future blog.
Enjoy Dave.