Quick SQL Date Functions and Queries

It’s easy to grab the date from a field in a database so long as you saved a timestamp field along with your data row. However, there are often times in developing stored procedures and advanced queries where one needs to look up events in the future, or past based on some other reference point from either the data at hand, or an arbitrary date, such as in a calendar.

A perfect example of this is setting up some sort of date book for say a Widgets Club, which meets every Monday. The Secretary then posts his/her meetings notes to the club’s website. You, being the savvy developer, don’t want to go in every Tuesday and put the new post up– you want it dynamic and to do it itself.

So, the question is: How do we get the date of last Monday to display the latest notes on our Widgets Club website? With the built in functions of SQL, we can easily derive the date of last Monday:

convert(varchar,dateadd(dd,0,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)),101)
as 'Monday'

While it looks like a huge mess, it is pretty straight forward.

Posted today, Friday 01/12/07, this query returns 01/08/2007 as Monday of this week.

Used inline with another query, or as part of the where clause, this code could be used to display the notes, probably something similar to this:

meetingdate, mettingnotes
meetingdate BETWEEN convert(varchar,dateadd(dd,0,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)),101) + ' 00:00:00' AND getdate()

This will get us anything that was posted between today and this week’s Monday.

Perhaps for this example a more practical solution would be to just sort by an auto_ID field descending, limiting to 1 result, but there are valid practical uses for this query.

It can also be altered to say get the previous weeks’ Monday date. Simply add in the -7 to the query like so:

convert(varchar,dateadd(dd,-7,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)),101)
as 'Last Monday'

Or, perhaps you have a product that only ships out on Monday’s. You want to tell the customer what Date their item will ship on. You could use a similar version of this query:

convert(varchar,dateadd(dd,7,DATEADD(wk, DATEDIFF(wk,0,getdate()),0)),101)
as 'Next Monday'

Delayed items? Shipping on next Tuesday instead? No problem. Just add 1 day to the DATEDIFF portion:

convert(varchar,dateadd(dd,7,DATEADD(wk, DATEDIFF(wk,0,getdate()),1)),101)
as 'Next Tuesday'

In all of these queries, you could sub out the getdate() function for a timestamp field from your database instead should it fit your application better, for example, the order date.

There are countless possibilities for generating past and future dated queries based off of a known date. Be it a data field, or the current date the script is run. Knowing how to use them is the first step. Once you are able to grasp how the dateadd, datepart, and other date functions work, you can truely see the built in power of the RDBMS system.

(Note: These were designed with MS SQL Server 2000. Your DBMS may handle some of these functions differently)

Leave a comment

Your email address will not be published. Required fields are marked *