Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Michael Offner-Streit and Tanja Stadelmann and Gert Franz and Pierre-Olivier Chassay and Paul Klinkenberg and Marcos Placona
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Michael Offner-Streit Tanja Stadelmann Gert Franz Pierre-Olivier Chassay Paul Klinkenberg Marcos Placona

SQL Date/Time BETWEEN And Comparison Operators Work With Floats

By
Published in Comments (21)

I don't want to beat a dead horse here, but I just discovered that you can interchange DATETIME and FLOAT values when using comparison operators like BETWEEN and > and <. So, if you accept that today, August 4, 2006 has the FLOAT equivalent of 38931.0, then you could query for records like this:

SELECT
	s.id,
	s.referer_url
FROM
	web_stats_session s
WHERE
	s.date_created BETWEEN 38930 AND 38932

In this case, 38930 stands for August 3, 2006 and 38932 stands for August 5, 2006. Keep in mind that the first value is inclusive (includes 08/03) and the second value is exlusive (excludes 08/05). I just think that is wicked cool.

I came across this answering a question on CF-Talk. Some guy was querying for dates but he knew that some dates would be null. I suggested this (after some testing):

SELECT
	id
FROM
	[table]
WHERE
	[date] BETWEEN
		ISNULL( date_started, 0 ) AND
		ISNULL( date_ended, getDate() )

In this example, the line:

ISNULL( date_started, 0 )

... tells the query to use the database date if it exists, and if it does not exist (is null), then use the zero date. This will not limit the query on that end of the BETWEEN. The second line:

ISNULL( date_ended, getDate() )

... tells the query to use the database date if it exists, and if it does not exist (is null), then use the current date. Now that I think about it though, if the second BETWEEN value is exclusive, you might have to add one day to the getDate().

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

Reader Comments

153 Comments

For a more database-agnostic solution, try:

COALESCE(date_started,0)

The other nice thing about COALESCE (besides working on every DB platform I can think of) is that you can also do multiple fallback options:

a = COALESCE(b,c,d,e,f,g)

15,880 Comments

Rick, I have never used the COALESCE function, but it looks very cool. I have to say that ISNULL() rocked my world at the very foundation and completely changed the way I run SQL statements that have NULL values. Who knows, COALESCE might be a second revolutions.

Thanks for the hot tip.

1 Comments

Only problem is if the 2 floats match

Works: s.date_created BETWEEN 38930 AND 38932

Does Not: s.date_created BETWEEN 38930 AND 38930

If you convert them to an INT, the above will work.

1 Comments

"... the second value is exlusive (excludes 08/05). I just think that is wicked cool."

Why is that cool? It breaks BETWEEN.

Also if you want to have a range with one day, you'd have
to use 39120.0 AND 39120.9.

Using INTs rather than FLOATS resolves this, because it ignores
time in a DateTime field.

1 Comments

ben
I have a problem
access database with the following SQL

" Select " & _
" EmployeeID, Date, MorningTimeIn, MorningTimeOut, " & _
" AfternoonTimeIn, AfternoonTimeOut, TotalHours " & _
" From " & _
" TimeClock " & _
" Where Date Like '%/%/%%%%'" & _
" And Date Between '" & DateT & "'" & _
" And " & _
"'" & DateF & "'" & _
" And " & _
" EmployeeID = " & "'" & EmpID & "'" & _
" Order By " & _
" Date Asc "

The problem is if DateF is 7/10/2007 & DateT is 8/4/2007 everything is fine. (I guess thats not a problem I get a proper retrieval of records)

However, if DateF is 7/9/2007 & DateT is 8/4/2007 everything goes wrong. (it retrieves record 7/9/2007 and records 8/1/2007-8/4/2007 so the problem is it is recognizing a 2 digit dayin the first ex. but when asked the look between a 1 digit day & and a 1 digit day does not recognise 2 digit days)

15,880 Comments

@Tim,

That is a very strange issue. Unfortunately, I know very little about Access. What language are you using (Not ColdFusion from the looks of it). I would say, force the day to always be two digits (ie. "09" vs "9"), but that is a ghetto hack.

Also, I don't like the looks of this: %/%/%%%%... if you are querying a "Date" field, should that always be the case? And I am not sure you need 4 % in a row for the year; I think one should suffice. Are you trying to check to see if that field is NULL?

1 Comments

i am facing problem when using the condition like this
this is one
create procedure P
(
@clientip varchar(50),
@Name varchar(50)
)
as

select * from tblReourse where clienip like @clientip and Name like @Name or Name is Null

here if we consider there are 6 records in the table like this

clientip Name
--------- ----------
123.2.3.4 sys
123.2.3.4 Null
123.2.3.4 www
123.2.3.4 Null
123.2.3.4 Null

here in this if i give the 'clientip' along wiht that ' Name' for the procedure it will return the corect records from the procedure

if i give only 'Name' then all the records will be returned there will be no filteringby for the particular name which we give..

i tried with isnull operator same problem exists can u plz give how to alter this procedure if i remove 'oR' condition then also we are not getting the correct answer

15,880 Comments

@Rajesh,

I have very little experience with stored procedures. But I am curious, when you say you only pass in the Name, are you passing in anything for the ClientIP? Are you passing in NULL for it?

1 Comments

I want to change the value of my tables column if a date column is over three days old, like deleting a reservation after three days, anyone got any ideas, am using JSP and SQL for my program.

1 Comments

m working in c#.when i retrieve records from access db then an exception throws that data type mismatch exception.
although i used a date field with type date/time and here in application when i write query
select * from tab wher date > '"+date1.text+"';
then grid cannot fill can any1 tell me the sol.
thanx

15,880 Comments

@shoaib,

Try outputting the SQL string rather than executing it. You might find that there is an error or unexpected value being used.

2 Comments

Hi.. isnull(somedate,0) would return "1900-01-01 00:00:00.000" for null.. my Q is.. how to return "-" instead?

I'm a newbie.. how do i change it to String?

15,880 Comments

@Sue,

I'm not sure you can really change data types mid-column (so to speak). For something like that, I'd just return NULL for the date and then on the programming side, check to see if the column value is a date:

<cfif isDate( query.dateColumn )> .... </cfif>

(or in whatever programming language you are using).

2 Comments

Thanks for your respond. I've figured out the solution.

isnull(CAST(CONVERT(varchar(11), a.icSUBDATE, 106)AS varchar(11)),'-') as SubmitDocToInsCo

it will return a dash '-' if the date is NULL..

Cheers!

2 Comments

Hii ben,

i have problem with comparision with date&time.i have one column which has datetime datatype. my data like "2010-11-20 12:45:50". now i have a another datetime like "2010-11-20 12:00:50". if i want to compare these in where clause then how can i do it ??

1 Comments

i have a problem when i want to select enddate -now()<=5 and enddate-now()>0 but it not work .
what could i do if want to select data from table when edate-now()lies between 0 to 5

1 Comments

the BETWEEN ... AND comparison operator is the equivalent of:

[expression] >= [value1] AND [expression] <= [value2]

In other words BOTH [value1] and [value2] are included in results, so I don't think this is correct:

"and the second value is exclusive"

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