Comparing string with date

  • Hi Experts,

    i have to write a where condition where i need to compare string with date

    i have 2 columns FROMDATE and TODATE with datatype varchar(9)

    the strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM

    i.e., 201401011 or 201401012

    so i need to chop off last character before using them in WHERE condition.

    now i need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.

    if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01

    the query i wrote is

    select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()

    AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)

    but it shows no data

    please help me pass through this

    Thank you.

  • everything needs to be a date in your second query.

  • Use the source column for the string-dates, as discussed here.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • As the first reply states, it would be easier to this using date or datetime data types, but assuming that you are locked in to using the varchar data type, you could do the following.

    I've included the table creation and inserting sample data steps for clarity.

    CREATE TABLE dbo.#DateTest

    (

    FromDate varchar(9)

    ,ToDate varchar(9)

    )

    Insert some sample data:

    INSERT INTO dbo.#DateTest VALUES ('201401221','201401292'),('201401092','201401161'),(NULL,'201402282'),('201401152',NULL)

    Declare a variable to hold today's date in integer format:

    DECLARE @Today int = CAST(CONVERT(varchar,getdate(),112) AS int)

    In your post you've got your condition round the wrong way...unless you system date is set to 19000101 or earlier!

    SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate, ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate FROM dbo.#DateTest

    WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today

    AND ISNULL(SUBSTRING(ToDate,1,8),99991231) > @Today

    Result:

    FromDateToDate

    2014012220140129

    1900010120140228

    2014011599991231

    EDIT: Removed an unnecessary CAST from the WHERE clause.

    Regards

    Lempster

  • Lempster (1/28/2014)


    As the first reply states, it would be easier to this using date or datetime data types, but assuming that you are locked in to using the varchar data type, you could do the following.

    I've included the table creation and inserting sample data steps for clarity.

    CREATE TABLE dbo.#DateTest

    (

    FromDate varchar(9)

    ,ToDate varchar(9)

    )

    Insert some sample data:

    INSERT INTO dbo.#DateTest VALUES ('201401221','201401292'),('201401092','201401161'),(NULL,'201402282'),('201401152',NULL)

    Declare a variable to hold today's date in integer format:

    DECLARE @Today int = CAST(CONVERT(varchar,getdate(),112) AS int)

    In your post you've got your condition round the wrong way...unless you system date is set to 19000101 or earlier!

    SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate, ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate FROM dbo.#DateTest

    WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today

    AND ISNULL(SUBSTRING(ToDate,1,8),99991231) > @Today

    Result:

    FromDateToDate

    2014012220140129

    1900010120140228

    2014011599991231

    EDIT: Removed an unnecessary CAST from the WHERE clause.

    Regards

    Lempster

    I'm just taking a short break so I don't have the time to write some code for that but the code above guarantees than an index seek is impossible (ie. Non-SARGable). If someone doesn't beat me to it, I'll try to get back to this tonight.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • THANK YOU FOR THE REPLY LEMPSTER

    IT WORKED:)

  • THANK YOU FOR THE REPLY

  • Post deleted. I made a mistake. I'll be back.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/29/2014)


    Post deleted. I made a mistake. I'll be back.

    A little history Jeff. Might save you some time.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Heh... crud. I posted the same mistake... I'll be back.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ChrisM@Work (1/29/2014)


    Jeff Moden (1/29/2014)


    Post deleted. I made a mistake. I'll be back.

    A little history Jeff. Might save you some time.

    You're right. That's insane and now I know what you meant in your original post. The OP changes perfectly good data to denormalized data and then tries to do a search on the denormalized data. I just don't get that and your original suggestion on this post is correct.

    Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/29/2014)


    Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:

    Could you post your solution anyway Jeff?

    Regards

    Lempster

  • ..

  • Lempster (1/29/2014)


    Jeff Moden (1/29/2014)


    Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:

    Could you post your solution anyway Jeff?

    Regards

    Lempster

    Sure. With the understanding that such data should never be stored in a table and that it should be normalized as a real DATETIME column and a separate column for the "time of day slot" indicator, here's how to solve this problem in a SARGable fashion. For those that don't know, "SARGable" has come to basically mean "can do an Index Seek if the correct supporting index is available".

    --=============================================================================

    -- Create a larger test table with the appropriate index

    -- Only adding the appropriate index is a part of the solution

    --=============================================================================

    --DROP TABLE dbo.#DateTest

    GO

    --===== Create the table, as before

    CREATE TABLE dbo.#DateTest

    (

    FromDate varchar(9)

    ,ToDate varchar(9)

    )

    ;

    --===== Insert the original 4 rows in the test data

    INSERT INTO dbo.#DateTest

    SELECT '201401221','201401292' UNION ALL

    SELECT '201401092','201401161' UNION ALL

    SELECT NULL ,'201402282' UNION ALL

    SELECT '201401152',NULL

    ;

    GO

    --===== Insert another 16380 similar rows

    INSERT INTO #DateTest

    SELECT * FROM #DateTest

    GO 12

    --===== Add the expected index

    CREATE INDEX IX_#DateTest

    ON #DateTest (FromDate,ToDate)

    ;

    --=============================================================================

    -- Demonstrate the the current solution will NOT do an Index Seek

    -- and a method that will. The Index Seek is followed by a nice

    -- high performance range scan

    --=============================================================================

    --===== Setup the variable for @Today to make testing easy

    DECLARE @Today DATETIME

    SELECT @Today = GETDATE() --Or whatever

    ;

    --===== This CANNOT do an Index Seek because of the formulas

    -- on the FromDate and ToDate columns (non-SARGable).

    SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate

    ,ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate

    FROM dbo.#DateTest

    WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today

    AND ISNULL(SUBSTRING(ToDate,1,8),99991231) > @Today

    ;

    --===== This DOES do an Index Seek because there are no forumulas

    -- on the table columns in the WHERE clause (SARGable).

    SELECT FromDate = FromDate -- ISNULL(FromDate,'19000101')

    ,ToDate = ToDate -- ISNULL(ToDate ,'99991231')

    FROM dbo.#DateTest

    WHERE (FromDate <= CONVERT(CHAR(8),@Today ,112)+'9' OR FromDate IS NULL)

    AND (ToDate >= CONVERT(CHAR(8),@Today ,112)+'0' OR ToDate IS NULL)

    ;

    Notice the neither FromDate or ToDate is contained in a formula.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff and I totally agree with you about doing this the proper way using DATETIME data type which is what I prefaced my first reply to the OP with.

Viewing 15 posts - 1 through 15 (of 18 total)

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