Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » DateTime Problem
Add to briefcase
13 posts, Page 1 of 2
12»»

DateTime Problem Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Mike Levan
Posted Monday, December 17, 2007 11:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 03, 2010 2:42 PM
Points: 1,023, Visits: 1,893
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
Post #433993
Adrienne-74055
Posted Monday, December 17, 2007 11:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:27 AM
Points: 199, Visits: 373
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.


Post #433995
GSquared
Posted Monday, December 17, 2007 11:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571
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
Post #433996
Mike Levan
Posted Monday, December 17, 2007 12:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 03, 2010 2:42 PM
Points: 1,023, Visits: 1,893
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.
Post #433998
ALZDBA
Posted Monday, December 17, 2007 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049
check out convert(datatype, yourcol, format)

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #434000
Adrienne-74055
Posted Monday, December 17, 2007 12:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:27 AM
Points: 199, Visits: 373
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'


Post #434004
Greg Snidow
Posted Monday, December 17, 2007 1:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 1,561, Visits: 2,313
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.
Post #434037
Sergiy
Posted Tuesday, December 18, 2007 1:46 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 4,550, Visits: 8,200
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.
Post #434164
Madhivanan-208264
Posted Friday, December 28, 2007 2:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461
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
Post #436946
ALZDBA
Posted Friday, December 28, 2007 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049
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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #436950
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.