Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at The Plaza Hotel 2012 (New York City) with:

SQL Date/Time BETWEEN And Comparison Operators Work With Floats

By Ben Nadel on
Tags: SQL

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().



Reader 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)

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

"... 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.

Reply to this Comment

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)

Reply to this Comment

@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?

Reply to this Comment

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

Reply to this Comment

@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?

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

@shoaib,

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

Reply to this Comment

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?

Reply to this Comment

@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).

Reply to this Comment

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!

Reply to this Comment

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 ??

Reply to this Comment

@Bhavin,

Have you tried just using a greater-than, less-than operator? Example:

WHERE dateOne > dateTwo

Reply to this Comment

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

Reply to this Comment

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"

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.