DateTime Problem

Question

Post reply

DateTime Problem

Mike Levan

SSChampion

Points: 14201
More actions
December 17, 2007 at 11:41 am

#62756

When i do the following query
select fromdate from emp where fromdate>'2/16/2007' (here fromdate is varchar)
I get the follwing result set
3/18/2004
9/28/2004
2/19/2004
5/24/2006
9/29/2004
5/10/2001
9/13/2001
6/30/2006
8/25/2000
7/18/2006
5/01/2007
8/02/2000
4/25/2001
8/17/2006
9/27/2006
but i need the dates greater than '2/16/2007' which is not doing
when I tried for less than and equal to it works just fine but am unable to get greater han

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Adrienne250 Right there with Babe Points: 737 More actions · Answer 1

Just cast fromdate (in the table) as a datetime like:

select fromdate from emp where cast(fromdate as datetime)>'2/16/2007'

as long as you are sure they are all valid datetimes otherwise the conversion will fail.

GSquared SSC Guru Points: 260824 More actions · Answer 2

It looks to me like your "fromdate" field might be char/varchar instead of datetime. In that case, you'll get any string that starts with a character > "2" in the example you gave.

If the column is datetime, try casting the date in the where clause as datetime.

If the column is char/varchar, you probably need to cast both as datetime, or you need to modify the table so the column is datetime (if that won't break anything else in the database/app/data connection layer). Casting both will result in a slow query (since it'll have functions on both sides of the equation), but will at least work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mike Levan SSChampion Points: 14201 More actions · Answer 3

yeah my Fromdate is a varchar as i told you and now i tried doing cast(fromdate as datetime) i get hte result set ok but with an error message

Msg 241, Level 16, State 1, Line 1

Syntax error converting datetime from character string.

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 4

check out convert(datatype, yourcol, format)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Adrienne250 Right there with Babe Points: 737 More actions · Answer 5

I don't know if you want to use a temp table but this should work

select *

into #temp

from emp

where isDate(fromdate) = 1

select fromdate

from #temp

where cast(fromdate as datetime)>'2/16/2007'

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

Mike Levan (12/17/2007)
yeah my Fromdate is a varchar as i told you and now i tried doing cast(fromdate as datetime) i get hte result set ok but with an error message
Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Since the field is varchar, you might have some records that are not proper dates. You could try this to make sure they are dates

SELECT

fromdate

FROM emp

WHERE fromdate IS NOT NULL

AND ISDATE(CONVERT(CHAR(10),fromdate,101)) = 0

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

This topic is named improperly.

There is no any problem with datetime.

The only problem is with column not being datetime.

Make it datetime and the problem will disappear.

_____________
Code for TallyGenerator

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

Mike Levan (12/17/2007)
When i do the following query
select fromdate from emp where fromdate>'2/16/2007' (here fromdate is varchar)
I get the follwing result set
3/18/2004
9/28/2004
2/19/2004
5/24/2006
9/29/2004
5/10/2001
9/13/2001
6/30/2006
8/25/2000
7/18/2006
5/01/2007
8/02/2000
4/25/2001
8/17/2006
9/27/2006
but i need the dates greater than '2/16/2007' which is not doing
when I tried for less than and equal to it works just fine but am unable to get greater han

1 Always use proper DATETIME datatype to store dates

2 Let the front end do the formation

If you have no option to change the datatype of the column, create a new column with datetime datatype;update it from varchar column and use that datetime column for further manipulations. Otherwise you have to end with with lot of castings or convertions

Madhivanan

Failing to plan is Planning to fail

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 9

follow Madhivanan's advise !

select fromdate

, convert(datetime,fromdate,100) as fromdate_datetime

, convert(char(23), convert(datetime,fromdate,100), 121) as frildate_datetimestring

from emp

where convert(datetime,fromdate,100) > convert(datetime,'2/16/2007',100)

order by convert(datetime,fromdate,100)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 10

I absolutely agree with Sergiy and Madhivinan... the root problem is that the column is the wrong data type. The datatype for the column should be/must be changed to DATETIME to avoid these problems in the future.

And, no, you can't rely on ISDATE because ISDATE('2007') will return a "1"...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 11

I absolutely agree with Sergiy and Madhivinan

Well except that you often mispell my name 😉