Wahyu Kurniawan Blog

Some of your need maybe at here





See also: Other Geeks@INDC

Select Date in Between

It turns out, that many people have asked the same question. How to select a list with date in between. I have a trick to solve this out.
 
If we have a list like this.
 
ID         InvoiceNum                    InvoiceDate
11         2007-0311-0004             3/11/2007 4:43:00 PM
12         2007-0311-0005             3/11/2007 5:02:00 PM
13         2007-0311-0006             3/11/2007 5:04:00 PM
14         2007-0328-0001             3/27/2007 10:55:00 PM
15         2007-0503-0001             5/3/2007 11:41:00 PM
16         2007-0503-0002             5/2/2007 11:42:00 PM
 
And we just create a query like this:
 
SELECT * FROM vwInvoice
WHERE InvoiceDate BETWEEN '05/02/2007' AND '05/02/2007'
 
The query result is row 16, is it right?
No... no... no...  As we know that DATETIME datatype consist of DATE and TIME. Actually if we look at that query, we miss the TIME is '00:00:00'. So the query will be like this.
 
SELECT * FROM vwInvoice
WHERE InvoiceDate BETWEEN '05/02/2007 00:00:00' AND '05/02/2007 00:00:00'
 
Is the result is row 16? You know the answer.


We can just get the DATE value with FLOOR and CAST function. Try this.

SELECT * FROM vwInvoice
WHERE
FLOOR(CAST(InvoiceDate AS FLOAT)) BETWEEN
FLOOR(CAST(CAST('05/02/2007' AS DATETIME) AS FLOAT)) AND
FLOOR(CAST(CAST('05/02/2007' AS DATETIME) AS FLOAT))

Let me know if you success or not with this trick.
Share this post: | | | |
Posted: May 08 2007, 11:59 AM by wahyukurniawan | with 3 comment(s)
Filed under:

Comments

Hendro said:

Why don't we just write it like this

SELECT * FROM vwInvoice

WHERE InvoiceDate BETWEEN '05/02/2007 00:00:00' AND '05/02/2007 23:59:59'

# May 8, 2007 1:19 PM

wahyukurniawan said:

It cannot like that if DATEFROM and DATETO was get from another table. It mean consist of DATE and TIME.

# May 8, 2007 2:53 PM

Gunawan said:

Will it work if change like this

SELECT * FROM vwInvoice

WHERE

FLOOR(CAST(InvoiceDate AS FLOAT)) BETWEEN

FLOOR(CAST(CAST(@StartDate AS DATETIME) AS FLOAT)) AND

FLOOR(CAST(CAST(@EndDate AS DATETIME) AS FLOAT))

# June 18, 2008 2:25 PM