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

datetime query issue Expand / Collapse
Author
Message
Posted Tuesday, September 1, 2009 12:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 1, 2011 10:58 AM
Points: 76, Visits: 906

Hi,

I am trying to run the below query but i get the error

select * from table where Convert(VARCHAR,dbo.table.starttime,103) between starttime and endtime
where starttime and endtime are datetime variables

Till recently this worked but all of sudden i get the below error with the query

Msg 8114, Level 16, State 5,
Error converting data type nvarchar to datetime.

Thanks for the help
Post #780461
Posted Tuesday, September 1, 2009 1:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 4,324, Visits: 10,589
Some rows contain invalid dates. You could try to identify those rows with something like

SELECT * FROM Table WHERE ISDATE(StartDate) = 0


but it won't catch all invalid dates. You could build a pattern and use it in a LIKE expression:

SELECT * FROM Table WHEREStartDate NOT LIKE '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]'



Anyway storing dates in a char column is not a good idea. Use a datetime column instead.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #780477
Posted Tuesday, September 1, 2009 4:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 1, 2011 10:58 AM
Points: 76, Visits: 906
Hi,

Thanks for the reply, when i run the below

SELECT * FROM Table WHERE ISDATE(StartDate = 0

i dont get any records returned.So i think table has valid datetime's

and also i am using datetime columns for all date related fileds


Thanks for the help



Post #780555
Posted Tuesday, September 1, 2009 4:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 1, 2011 10:58 AM
Points: 76, Visits: 906
Hi,

Sorry missed this

SELECT * FROM Table WHEREStartDate NOT LIKE '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]'

The above returned the records

Thanks for the help
Post #780557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse