Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Matthew Bourke and Jim Cumming
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Matthew Bourke Jim Cumming

Problems With Storing Date And Time Separately In SQL Server

By
Published in

I just added links for previous and next blog posts at the bottom of every blog entry detail page. I stumbled a bit when trying to query for the previous and next posts because of the way I store the date and time of posting in the database. Over time I have learned it is easier to store date and time separately where date is in a DATETIME field (with zero time) and the time is stored as 24 hour time in a VARCHAR(5) field (ex. 17:15). This makes manipulation and date comparison easy... most of the time.

In this case, however, it did cause a slight problem. Having already queried for the detail blog post, my initial query went something like this (for shorter code, assume that @id is the current entry ID and that @date_posted is the current entry's date of posting):

<!--- Query for next post. --->
<cfquery name="REQUEST.NextQuery" datasource="..." username="..." password="...">
	SELECT TOP 1
		b.id,
		b.name,
		b.date_posted,
		b.time_posted
	FROM
		blog_entry b
	WHERE
		<!--- Exclude the current entry. --->
		b.id != @id
	AND
		<!--- Get only newish entries. --->
		b.date_posted >= @date_posted
	ORDER BY
		b.date_posted ASC,
		b.time_posted ASC
</cfquery>


<!--- Query for prev post. --->
<cfquery name="REQUEST.PrevQuery" datasource="..." username="..." password="...">
	SELECT TOP 1
		b.id,
		b.name,
		b.date_posted,
		b.time_posted
	FROM
		blog_entry b
	WHERE
		<!--- Exclude the current entry. --->
		b.id != @id
	AND
		<!--- Get only oldish entries. --->
		b.date_posted <= @date_posted
	ORDER BY
		b.date_posted DESC,
		b.time_posted DESC
</cfquery>

This worked most of the time so the problem wasn't immediately apparent. Sometimes though, the previous and next entries were the same link. After doing some CFDump'ing, I realized that the time_posted field was causing issues, or rather, the fact that it was stored separately was causing issues.

Take for example the three posts:

A. 7/27/2006 - 10:00 AM
B. 7/27/2006 - 09:00 AM
C. 7/26/2006 - 12:00 PM

Now, let's assume that post (B) is the current post. If I were to query for the next and previous posts based only on the date field (as the queries above do), I would get post (A) for both the previous and next results since date equality is allowed. After that, sorting on the time posted is moot as the data is already invalid.

To overcome this, I had to start taking the time into account in the WHERE clause, not just in the ORDER BY. The result is a little hacky, but seems to do the trick (for shorter code, assume that @id is the current entry ID, that @date_posted is the current entry's date of posting, and that @time_posted is the current entry's time of posting):

<!--- Query for the next post. --->
<cfquery name="REQUEST.NextQuery" datasource="..." username="..." password="...">
	SELECT TOP 1
		b.id,
		b.name,
		b.date_posted,
		b.time_posted
	FROM
		blog_entry b
	WHERE
		<!--- Exclude the current entry. --->
		b.id != @id
	AND
		<!--- Get only newish entries. --->
		b.date_posted >= @date_posted
	<!---
		We have to fudge it a bit here to take the time of
		posting into account.
	--->
	AND
		(
			<!---
				We only need to take the time of posting into account
				if we are looking at the date of the current post.
				Otherwise, time of posting is irrelevant.
			--->
			CASE
				WHEN
					b.date_posted = @date_posted
				THEN
					CASE
						WHEN
							b.time_posted >= @time_posted
						THEN
							1
						ELSE
							0
					END
				ELSE
					1
			END
		) = 1
	ORDER BY
		b.date_posted ASC,
		b.time_posted ASC
</cfquery>


<!--- Query for the previous post. --->
<cfquery name="REQUEST.PrevQuery" datasource="..." username="..." password="...">
	SELECT TOP 1
		b.id,
		b.name,
		b.date_posted,
		b.time_posted
	FROM
		blog_entry b
	WHERE
		<!--- Exclude the current entry. --->
		b.id != @id
	AND
		<!--- Get only oldish entries. --->
		b.date_posted <= @date_posted
	<!---
		We have to fudge it a bit here to take the time of
		posting into account.
	--->
	AND
		(
			<!---
				We only need to take the time of posting into account
				if we are looking at the date of the current post.
				Otherwise, time of posting is irrelevant.
			--->
			CASE
				WHEN
					b.date_posted = @date_posted
				THEN
					CASE
						WHEN
							b.time_posted <= @time_posted
						THEN
							1
						ELSE
							0
					END
				ELSE
					1
			END
		) = 1
	ORDER BY
		b.date_posted DESC,
		b.time_posted DESC
</cfquery>

As you can see above, I now have to take both date and time into account when doing the comparison (not just the sorting) since they are stored separately. However, time is not always relevant; if the entries were posted on different days, I only care about the time posted when sorting, not elimination. I only have to take time into account for entries posted on the same day. Not the end of the world, but it makes for a less elegant query. I still think that in the long run, storing date and time separately has many more benefits than draw backs.

Want to use code from this post? Check out the license.

Reader Comments

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel