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

# re: SQL DateTime and comparison

Pls, i want to pull date range from a particular date for instance '1/1/2009' AND '1/1/2010'

Left by Apantaku Olaleke R. on Mar 25, 2010 7:18 PM

# re: SQL DateTime and comparison

Where (convert(datetime, admit_date, 102) >= '01/01/2000' and (convert(datetime, admit_date, 102) <= '12/31/2000'

Getting invalid syntax near 12/31/2000

Any ideas??

Left by Frank on Apr 21, 2010 3:41 PM

# replica jewelry

Where (convert(datetime, admit_date, 102) >= '01/01/2000' and (convert(datetime, admit_date, 102) <= '12/31/2000'

G

Left by replica jewelry on Apr 29, 2010 5:13 AM

# re: SQL DateTime and comparison

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

1. WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
2. WHERE Convert(varchar(20), OrderDate, 101)

Left by wholesale laptop adapter on May 23, 2010 2:32 PM

# re: SQL DateTime and comparison

Hi i want all the records that are created or updated in a particular day from time 00:00:00 hrs to 23:59:59 can any please help me

KM......

Left by krish on Aug 09, 2010 7:39 AM

# re: SQL DateTime and comparison

Try this:

SELECT [Fields]
FROM [Table]
WHERE ((DateDiff("d",[Variable],Now())<=1));

Left by Tim on Sep 22, 2010 10:09 PM

# re: SQL DateTime and comparison

I used this:
WHERE @datetime BETWEEN '01/01/2006' DATEADD(day, 1, '01/01/2006')

Left by Navid Forhad on Oct 28, 2010 2:42 PM

# re: SQL DateTime and comparison

thanx
3 most efficient worst performance
very informitive and nice looking blog
that was help

Left by wezza on Jan 03, 2011 12:33 AM

# re: SQL DateTime and comparison

I used SQL to do much computationals and it worked good but the SQL does not have the color looking good for end user so I am work on making the SQL have color.

Can somebody please tell me the SQL command to change color skin of database to suit portable computer style.

Left by James on Jan 28, 2011 12:17 PM

# re: SQL DateTime and comparison

I also prefer dateadd sql function (or date_add / date_sub MySQL functions.

Thanks for this post, I never knew there were so many possibilities regarding dates :-)

Left by jazkat on Feb 01, 2011 10:09 AM

# re: SQL DateTime and comparison

Hi All,

I am wondering how can we compare if two date variables are in the same format in TSQL.

E,g:

@date1 = '2011-02-16 00:00:00.000'
@date2 = '2011/02/16'

so the above two variables are in two different formats, any idea how we can identify they are in two different formats. Any built in function or any logic idea would be appreciated.

Left by somesh on Feb 16, 2011 6:58 PM

# clothing manufacturer

I used SQL to do much computationals and it worked good but the SQL does not have the color looking good for end user so I am work on making the SQL have color.

Left by clothing manufacturer on Mar 06, 2011 3:30 PM

# re: SQL DateTime and comparison

You do a good job, rally.

Left by bag manufacturer on Mar 08, 2011 1:22 PM

# re: SQL DateTime and comparison

tanks for your sharing,it is very useful to learn

Left by laptop adapter wholesaler on May 24, 2011 8:48 AM

# re: SQL DateTime and comparison

I am used to code like this:
# WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
But Your idea "WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'" looks better for me. I am going to check this out.

Left by Rollladen on Jun 08, 2011 10:29 AM

# re: SQL DateTime and comparison

Guys relax don't make too much fuss about it.
Just think simple , as we know sql server is capable of comparing date in all available formats.
just prefer simple which you will understand like this
select * from tblUserEntery where date < '30-July-2011'
this will give all data less then 30-july-2011 leave the rest calculations to Sql server and if data is too large then make index on that particular date field.

Left by Xitij Thool on Aug 01, 2011 7:14 AM

# re: SQL DateTime and comparison

thanx man your query helped a lot i was stuck by using like command in datetime but now day(date) works fine

Left by saurabh on Sep 05, 2011 7:49 AM

# re: SQL DateTime and comparison

@sam answer works great.

Left by furqan on Oct 10, 2011 1:15 PM

# re: SQL DateTime and comparison

guys is it possible in SQL to chck the system date of the computer? What would be the syntax of that query? thanks thanks

Left by nooby on Nov 18, 2011 8:11 AM

# re: SQL DateTime and comparison

Anyone know how to compare a date in a date range which the Fromdate and Todate are not constant, they are passed in as a parameter ?

Left by Ann on Jan 24, 2012 5:58 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 2 and 2 and type the answer here:

STEFANO DEMILIANI