Post reply

if isdate(5647) returns true, how do you determine if a value is a date

  • September 20, 2007 at 9:51 am

    #181164

    I'm trying to determine if a field has a date on it so I can update the field with a different date format.  The issue I'm having is that, that database field also contains numeric data. 

    One of the fields that is not formatting correctly contains the value 5647.  The issue is occuring because

    Isdate(5647) returns true. 

    Does anyone know try this statement returns true and if there is a better accurate way to determine if a value contains a date?

    The date values in the db typically look like this.  Mar 5 1999.

    Thanks...

  • September 20, 2007 at 10:00 am

    #734994

    Maybe use a combo where isdate = 1 and isnumeric = 0 ?

  • September 20, 2007 at 10:06 am

    #734996

    That's a good idea.  Thanks.  I am still curious if anyone knows why isdate returns true. 

  • September 20, 2007 at 10:33 am

    #735003

    Because at the storage level Sql server interprets datetime as 2 separate integers.

    1 integer is the # of days past 01/01/1900. and the second is the Milliseconds past Midnight.

    So if you do some tests

    select convert(Datetime,0)

    Returns;

    1900-01-01 00:00:00.000

    select convert(Datetime, 5647)

    Returns:

    1915-06-19 00:00:00.000

     

  • September 20, 2007 at 11:48 am

    #735030

    --As Ray identified, an integer will return as a valid date, so you need to filter the results with what date ranges are acecptable for you:

    declare @someval int,

            @MinDate datetime,   --minimum acceptable date

            @maxdate datetime    --maximum acceptable date

    set @someval = 5347

    set @MinDate = '01/01/1990'

    set @Maxdate = '12/31/2050'

    select convert(datetime,@someval)  --1915-06-19 00:00:00.000

    SELECT CASE WHEN Isdate(@someval) = 1 THEN CASE WHEN  convert(datetime,@someval) between @MinDate and @Maxdate THEN convert(datetime,@someval) ELSE NULL END

                ELSE NULL END AS someDate

    --the above returns null, because the date 06/19/1915 is not between my acceptable dates.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 20, 2007 at 2:05 pm

    #735064

    I had also tried converting the date to a datetime and got the same date.  Initially, I just didn't see how 5347 translated to 6/19/1915, but if your adding days from 1900 then 5647 days, it does come out to a little over 15 years. 

    The combination of using isnumeric and Lowell's suggestion will work as a workaround.  Thanks...

Viewing 6 posts - 1 through 5 (of 5 total)

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