Post reply

datetime query issue

  • September 1, 2009 at 12:39 am

    #133280

    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

    #1047332

    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

    #1047392

    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

    #1047394

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply