Using LessThan with dates? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using LessThan with dates?

Author

Topic

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-04 : 16:24:09

Why doesnt this work:

... WHERE convert(datetime,DueDate,103) > convert(datetime,Getdate(),103)

How can I accomplish querying for overdue duedates?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-04 : 16:29:20

GETDATE() already returns a datetime, you don't need to convert it, and the style parameter won't affect it's value. Same applies to your DueDate column if it's a datetime datatype. Have you tried this yet:

WHERE DueDate < Getdate()

(You said less than "<" in the subject put had greater than ">" in the WHERE clause, I changed it above)

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-04 : 16:48:49

quote:
Originally posted by robvolk

GETDATE() already returns a datetime, you don't need to convert it, and the style parameter won't affect it's value. Same applies to your DueDate column if it's a datetime datatype. Have you tried this yet:

WHERE DueDate < Getdate()

(You said less than "<" in the subject put had greater than ">" in the WHERE clause, I changed it above)

My date time is a char field in 103 format thats why we are converting it, then getdate needs to also be in 103 so we convert that as wel. This works for other queries.

Using convert(datetime,DueDate,103) < convert(datetime,Getdate(),103) works but also returns records that do not have a due date. Why would this be?

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-04 : 16:52:08

Hmm.. this seems to work:
NOT DueDate='' AND convert(datetime,DueDate,103) < convert(datetime,Getdate(),103

Can anyone spot any problems with using the above? IS NOT NULL doesnt seem to work.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 06:58:59

quote:
Originally posted by rtown
My date time is a char field in 103 format thats why we are converting it, then getdate needs to also be in 103 so we convert that as wel. This works for other queries.

Using convert(datetime,DueDate,103) < convert(datetime,Getdate(),103) works but also returns records that do not have a due date. Why would this be?

Bold part is not correct. You convert char field into dateTime format, so you now simply compare one dateTime value with another one.

convert(datetime,DueDate,103) < getDate()

About why it returns data without due date:
What is a char value you pass to query when there is no due date? Can it be something that is converting to min date value or something like that?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-07 : 15:24:07

quote:
Bold part is not correct. You convert char field into dateTime format, so you now simply compare one dateTime value with another one.
convert(datetime,DueDate,103) < getDate()

I dont think that is correct. Formatting my DueDate from a char field to date 103 produces a char value into 21/02/2011. Getdate with no conversion produces 02/21/2011.

So if I dont convert them both, I end up with 21/02/2011 < 02/21/2011.
Remember I am converting the first one to 103 not to change the format but the have it recognized as a date.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-03-07 : 16:08:35

When you convert a char field to a datetime - it is not defined by a specific format. It is defined by the data type.

What you are seeing is how the client tool displays that datetime value - which is based upon the date format for the server and your client system settings.

Jeff

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-08 : 04:22:32

quote:
Formatting my DueDate from a char field to date 103 produces a char value into 21/02/2011. Getdate with no conversion produces 02/21/2011.

Jeff already explained, but you can also test it, see what is the output of following query:

select convert(dateTime, '21/02/2011', 103)

Bold part from your explanation is wrong. With convert you do not change a char column format, you convert char representation of date in specific format into internal sql server binary date format. When you select that value later it is displayed according to your client setting unless you force explicit format by convert function.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources