SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateTime Problem


DateTime Problem

Author
Message
Mike Levan
Mike Levan
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 1893
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
Adrienne250
Adrienne250
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 430
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
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55065 Visits: 9730
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
Mike Levan
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 1893
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.
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28425 Visits: 8986
check out convert(datatype, yourcol, format)

Johan


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Adrienne250
Adrienne250
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 430
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
Greg Snidow
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4183 Visits: 2494
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
Sergiy
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24479 Visits: 12464
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.
Madhivanan-208264
Madhivanan-208264
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1505 Visits: 476
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
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28425 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search