datetime query issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » datetime query issue

datetime query issue

Rate Topic

Display Mode

Topic Options

Author

Message

forum member

Posted Tuesday, September 01, 2009 12:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 01, 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

spaghettidba

Posted Tuesday, September 01, 2009 1:14 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #780477

forum member

Posted Tuesday, September 01, 2009 4:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 01, 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

forum member

Posted Tuesday, September 01, 2009 4:33 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 01, 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 »

Permissions