how to compare datetime object in sql

Question

5.00/5 (2 votes)

See more:

SQL-Server-2005

I want to compare two datetime object.
my table

SQL

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [datevalue] [datetime] NOT NULL
) ON [PRIMARY]


insert into Table_1 values(1,convert(datetime,'01/11/2014',103))
insert into Table_1 values(2,convert(datetime,'02/11/2014',103))
insert into Table_1 values(3,convert(datetime,'03/12/2014',103))
insert into Table_1 values(4,convert(datetime,'04/12/2014',103))
insert into Table_1 values(5,convert(datetime,'05/12/2014',103))



declare @date nvarchar(10);
set @date='03/12/2014'
select convert(varchar(10),datevalue,103) from Table_1 
where convert(varchar(10),datevalue,103)=convert(varchar(10),@date,103) //this is correct

but
set @date='01/12/2014'
select convert(varchar(10),datevalue,103) from Table_1 
where convert(varchar(10),datevalue,103)>convert(varchar(10),@date,103)

it gives 
02/11/2014
03/12/2014
04/12/2014
05/12/2014

this query should give only three records

03/12/2014
04/12/2014
05/12/2014

Posted 25-Aug-14 19:40pm

Member 7909353

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kumarbs · Accepted Answer · 2014-08-25T20:03:00

Solution 1

Never convert the date formats to string whenever you are comparing. When you do this the date behaves as a varchar value rather than the date, hence the comparison works on varchar.
Use the following query.

SQL

declare @date nvarchar(10);
 set @date='01/12/2014'
select convert(varchar(10),datevalue,103) from Table_1
where convert(datetime,datevalue,103)>convert(datetime,@date,103)

Hope it solves your problem.

Posted 25-Aug-14 20:03pm

Kumarbs

Updated 25-Aug-14 21:03pm

v2

Comments

Member 7909353 26-Aug-14 2:32am

But sometimes I need only time sometimes only date,I have to convert

Member 7909353 26-Aug-14 3:02am

insert into Table_1 values(101,convert(datetime,'03/06/2013',103))

select convert(datetime,datevalue,103) from Table_1
where convert(varchar(10),datevalue,103) between convert(varchar(10),'02/11/2014',103)
and convert(varchar(10),'03/11/2014',103)
this gives '03/06/2013' value also why?

Kumarbs 26-Aug-14 3:07am

Ofcourse you can get the output but it is not based on the datetime, it works on strings. May be the string values are correct ones which satisfies your conditions. Try to select those strings in select command and try comparison with out convert then you knows the difference.