SQL DateTime and comparison

<< Office Live will be mobile? | Home | C# Singleton Code Snippet >>

SQL DateTime and comparison

Today I had to modify some SQL code for an application and this work was the start for a discussion: what's the best way to check for a SQL DateTime field with only a given date?

For example, I've an Order table with a DateTime field called OrderDate. I want to retrieve all orders where the order date is equals to 01/01/2006. What's the best way?

WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
WHERE OrderDate LIKE '01/01/2006%'
WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'
...

In my opinion the best way in terms of performances is the 5) and it's one of my personal "best practices" when this type of comparison is required. It requires 2 tests but it's better than calling special T-SQL functions like the others.

Any other ideas?

P.S. What is absolutely to avoid is the using of the LIKE function... terrible!

Print | posted on Thursday, January 19, 2006 11:06 AM

Comments on this post

# re: SQL DateTime and comparison

I like

where datepart(dy,OrderDate) = 1 and datepart(yyyy,OrderDate) = 2006

Left by Dave Burke on Jan 19, 2006 12:30 PM

# re: SQL DateTime and comparison

BETWEEN might be an option, as in:

WHERE OrderDate BETWEEN '01/01/2006' AND '01/02/2006'

Left by Simon on Jan 19, 2006 1:01 PM

# re: SQL DateTime and comparison

I think that BETWEEN is exactly like the solution N° 5, good.
Thanks Dave, I've forgotten the DATEPART solution.
However guys... would be interesting to know how is the impact on performances.

Left by Stefano Demiliani on Jan 19, 2006 6:33 PM

# re: SQL DateTime and comparison

WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006' No: ['02/01/2000' > '01/01/2020']!

I think that best way is:

WHERE OrderDate BETWEEN '20060101' AND '20060102'

Left by David on Jan 20, 2006 3:22 PM

# re: SQL DateTime and comparison

WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006' No: ['02/01/2000' > '01/01/2020']!

I think that best way is:

WHERE OrderDate BETWEEN '20060101' AND '20060102'

Left by David on Jan 20, 2006 3:22 PM

# re: SQL DateTime and comparison

I have someone like that:
WHERE F.Date >= @Date1 AND F.Date <= @Date2";

where Date1, and Date 2 come in DateTime parameters with the assing

SqlParameter par_Date1 = cm.Parameters.Add("@Date1", SqlDbType.DateTime);
SqlParameter par_Date2 = cm.Parameters.Add("@Date2", SqlDbType.DateTime);

par_Date1.Value = Date1;
par_Date2.Value = Date2;

What is the problem....

Left by on Nov 22, 2006 7:23 PM

# re: SQL DateTime and comparison

What's your problem? Do you want to test if F.Date is on a given range? It seems correct.

Left by Stefano Demiliani on Nov 22, 2006 7:29 PM

# re: SQL DateTime and comparison

I use the following query

select * from tableName where date between '15/01/2007 00:00:00' and '22/01/2007 23:59:59'

for the result i m getiing all the record where the day (dd) is between 15 and 22 from any month and any year.

Can someone help me on this problem

thanks

Left by Sjay on Jan 22, 2007 2:56 AM

# re: SQL DateTime and comparison

ops got it ..... date column type is vchar not
datetime.

Thats makes it compare it as a vchar not date

coool

Left by Sjay on Jan 22, 2007 3:08 AM

# re: SQL DateTime and comparison

wonderful, that solve my problem

Left by kenson goo on Apr 30, 2007 2:44 PM

# re: SQL DateTime and comparison

Here is the best way, extend #2 like this:
CAST(CONVERT(varchar(10),OrderDate,101) as datetime)

Left by Sam on Oct 17, 2007 12:00 AM

# re: SQL DateTime and comparison

(CONVERT(varchar, wijziging_datum, 0) = CONVERT(varchar, CONVERT(datetime, '4/23/2008 3:26:24 PM')))

Left by JAY on Apr 23, 2008 3:32 PM

# re: SQL DateTime and comparison

I just want to ask, if i want to change the value of a field after three days, how do i compare the date field in my database to trigger the change?

Thank you.

Left by lynchpin on Apr 25, 2008 11:12 PM

# re: SQL DateTime and comparison

I think you've to schedule a T-SQL procedure via SQL Server Agent that every day simply monitors the date field and, after 3 days, change the field value.
To check the date, you can use the DATEADD function: DATEADD(day, 3, YourFieldDate).
Check the DATEADD sintax here:
http://msdn2.microsoft.com/en-us/library/aa258267(SQL.80).aspx

Left by Stefano Demiliani on Apr 27, 2008 2:11 PM

# re: SQL DateTime and comparison

I like this datetime / datediff /dateadd functions because I keep forgetting how they work. Now I remember the function that I need is convert, and here is how I used to do it: SELECT OrderNumber, AccountNo, Amount, Shipping, Tax, AmountPaid, EditedOn, LastName, Company
FROM view_orders
WHERE (EditedOn < CONVERT(datetime, '7/31/2008 11:59:59 PM')). Pretty cool blog, Stefano, Thank you!!!

Left by yogi on Aug 15, 2008 12:09 AM

# re: SQL DateTime and comparison

We do something like ...where date_v - sysdate < 1 and date_v - sysdate > 0... to get records that are from today, for example, but you can generalize it for any date comparison though. If you don't like < 1 you can use trunc(...) = 0 too.

Left by Thracx on Sep 12, 2008 9:42 PM

# re: SQL DateTime and comparison

I want some SQL code for an application : what's the best way to check for a SQL DateTime field with only a given date?

For example, I've an project table with a DateTime field called lastmodifieddate. I want to retrieve all orders where the order date is equals to 01/01/2006 12:10:00 AM. What's the best way?

Left by Amira on Nov 06, 2008 10:48 AM

# re: SQL DateTime and comparison

thanks!

Left by steve on Jul 07, 2009 3:12 PM

# re: SQL DateTime and comparison

Date format
yyyymmdd
mm/dd/yyyy
dd-mm-yyyyy

Left by tan on Aug 11, 2009 10:54 AM

# re: SQL DateTime and comparison

WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'

is the best in my opinion,
I don't thinks 4 will work couse it is had to anticipate the result

Left by software developers on Aug 14, 2009 10:25 AM

# re: SQL DateTime and comparison

i have a column [Date] is in my table named Archive, there are so many records in it, i need to delete the records from the table that passes 6 months in my table.any record in the table passes 6 month of interval will be deleted whenever i move the mouse on a form...

Left by Shamal on Dec 02, 2009 9:45 AM

# re: SQL DateTime and comparison

COMO VAS

Left by HOLA on Jan 15, 2010 11:41 PM

Your comment:

Title:

Name:

Email: (never displayed)

(will show your gravatar)

Website:

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Please add 7 and 7 and type the answer here:

STEFANO DEMILIANI