September 1, 2009 at 12:39 am
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
September 1, 2009 at 1:14 am
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
September 1, 2009 at 4:30 am
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
September 1, 2009 at 4:33 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy