Post reply

Filter using a datetime field

  • July 7, 2011 at 8:04 am

    #243935

    I am having trouble with what seems should be simple. In tableA I have a column named PERIOD. Period holds a value of YearMonth (YYYYMM or 201105.) In tableB I have a column named INV_SER_DT which is a datetime field.

    I populate tableA.PERIOD from tableB.INV_SER_DT using CONVERT(VARCHAR(6), INV_SER_DT, 112) AS [YYYYMM] and this works great. However, when I query tableA with a join on tableB I am having difficulties getting the filter right. What is the best syntax to say WHERE tableA.PERIOD = tableB.INV_SER_DTs YEAR+MONTH? I can't use YEAR(INV_SER_DT)+MONTH(INV_SER_DT) because the MONTH function would return 20115 instead of 201105.

    Thanks for any advise your guidance you might provide.

    Sincerely,

    Lee

  • July 7, 2011 at 9:20 am

    #1349773

    What's wrong with comparing it to the result of the function call you used to populate PERIOD?

    CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 7, 2011 at 10:20 am

    #1349812

    Another option if you want to stick with the dates and do math as integers is (YEAR(INV_SER_DT)*100000)+MONTH(INV_SER_DT). By multiplying the year function (2011) by 100000 and then adding the month (05) you should still get 201105.

  • July 7, 2011 at 11:43 am

    #1349902

    opc.three (7/7/2011)

    What's wrong with comparing it to the result of the function call you used to populate PERIOD?

    CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

    This is exactly what I was trying but having issues with... or I thought I was. Seemed in my much longer and complete script there was another minor error which was throwing it off. This works exactly as expected.

    Thanks,

    Lee

  • July 7, 2011 at 12:01 pm

    #1349910

    lgoolsby 86333 (7/7/2011)

    opc.three (7/7/2011)

    What's wrong with comparing it to the result of the function call you used to populate PERIOD?

    CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

    This is exactly what I was trying but having issues with... or I thought I was. Seemed in my much longer and complete script there was another minor error which was throwing it off. This works exactly as expected.

    Thanks,

    Lee

    Good news, happy you got it sorted 🙂

    As an aside, wrapping a column in a CONVERT function essentially prevents SQL Server from utilizing the index on said column. If performance becomes an issue for you and it's traced back to the column you do have this option:

    1) Adding a computed column to tableB, maybe named INV_SER_DTs_112, defined as CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

    2) Add an index that contains the column INV_SER_DTs_112 that helps your queries and then JOIN to INV_SER_DTs_112

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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