Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DateTime Problem Expand / Collapse
Author
Message
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
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, February 03, 2014 11:43 AM
Points: 199, Visits: 381
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
Posted Monday, December 17, 2007 11:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
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
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
Posted Monday, December 17, 2007 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
check out convert(datatype, yourcol, format)



Johan


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
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, February 03, 2014 11:43 AM
Points: 199, Visits: 381
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
Posted Monday, December 17, 2007 1:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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
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: Monday, April 21, 2014 10:48 PM
Points: 4,570, Visits: 8,317
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
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, January 22, 2014 3:07 AM
Points: 329, Visits: 468
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
Posted Friday, December 28, 2007 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


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 12»»

Permissions Expand / Collapse