Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Data Warehousing Dates

    Sooooooo

    I am going out to another data warehouse to utilizes some time data. Well the time is stored ass YYYYMM Varchar. I have tried to convert this using Convert(Date, COLUMN, 120) and Cast. I decided to utilize STUFF to insert a - in the 5th spot and then join that out to another warehouse to a datatime date.

    How do you all deal with this nonsense?

    Had to cast the datetime to varchar in the join. My join was basically this, STUFF(REPORTDATE,5,0,'1') + '-01' = CAST(SQL_DATE as VARCHAR)

    I maybe over engineering this thing!
    Last edited by VLOOKUP; 08-21-15 at 17:38.

  2. Best Answer
    Posted by Pat Phelan

    "The idea of the date table is to allow you to pre-calc all of that kind of date conversion gibberish.

    This is an asymmetric date lookup, so I'd add two columns to the date table. One going from full dates to the YYYYMM, which would have an entry in every row. One column going from YYYYMM to a date which has a YYYYMM value for the first of the month and NUL for every other day of the month.

    So try this widget out as an example:
    Code:
    ; WITH numbers AS (
    SELECT 2048 * z1.number + z2.number AS number
       FROM master.dbo.spt_values AS z1
       CROSS JOIN master.dbo.spt_values AS z2
       WHERE  'P' = z1.type
          AND 'P' = z2.type
    ), raw_dates AS (
    SELECT DateAdd(day, number, Cast('1753-01-01' AS Date)) AS base_date
       FROM numbers
       WHERE  number < 455456
    ), cooked_dates AS (
    SELECT base_date
    ,  10000 * Year(base_date) + 100 * Month(base_date) + Day(base_date) AS date_number
    ,  CASE WHEN 1 = Day(base_date) THEN Convert(CHAR(6), base_date, 112) END AS from_yyyymm
    ,  Convert(CHAR(6), base_date, 112) AS to_yyyymm
    ,  base_date AS when_valid
    ,  DateAdd(day, 1, base_date) AS when_invalid
    ,  Year(base_date) AS calendar_year
    ,  Month(base_date) AS calendar_month
    ,  Day(base_date) AS calendar_day
    ,  CASE WHEN Month(base_date) > 9 THEN  1 ELSE 0 END + Year(base_date) AS federal_year
    ,  CASE WHEN Month(base_date) > 9 THEN -9 ELSE 3 END + Month(base_date) AS federal_month
    ,  Day(base_date) AS federal_day
    ,  DateName(month, base_date) AS month_name
    ,  DateName(dw, base_date) AS day_of_week
    ,  Left(DateName(dw, base_date), 3) AS dow
         FROM raw_dates
    )
    SELECT *
       FROM cooked_dates
    By indexing the table on the when_valid and when_invalid columns, you can easily hunt down the appropriate row for any temporal expression (not just Getdate(), but any date, datetime, smalldatetime, datetime2, etc.) using something like:
    Code:
    SELECT *
       FROM myDateTable
       WHERE  when_valid <= GetDate() 
          AND GetDate() < when_invalid
    When you convert your data to the YYYYMM, you can simply use the to_yyyymm column and you'll get the correct answer. When you must convert back, use the from_yyyymm column (this handles the asymmetry in the date format because of the CASE expression).

    Date tables are your friends!

    Oh yeah, I threw in the federal values just to make life simpler for folks who need a fiscal calendar. Since many fiscal calendars match the United States Government and their fiscal year starts on October 1, that's the example that I used.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Start here: http://www.brentozar.com/archive/201...e-table-video/

    The basic idea is to create a table with a row for every date that you find useful to manage your data, I recommend from one to thirty years before the earliest possible date of interest through one century after the latest day of interest. This might seem like an outrageous overkill at first, until you have to deal with mortgages, warranties, etc.

    Take a look at it, then you'll probably have more questions. You know how to find me!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Hey Pat what do you think about that join? Yep as you can imagine it's not pulling back anything LOL

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The idea of the date table is to allow you to pre-calc all of that kind of date conversion gibberish.

    This is an asymmetric date lookup, so I'd add two columns to the date table. One going from full dates to the YYYYMM, which would have an entry in every row. One column going from YYYYMM to a date which has a YYYYMM value for the first of the month and NUL for every other day of the month.

    So try this widget out as an example:
    Code:
    ; WITH numbers AS (
    SELECT 2048 * z1.number + z2.number AS number
       FROM master.dbo.spt_values AS z1
       CROSS JOIN master.dbo.spt_values AS z2
       WHERE  'P' = z1.type
          AND 'P' = z2.type
    ), raw_dates AS (
    SELECT DateAdd(day, number, Cast('1753-01-01' AS Date)) AS base_date
       FROM numbers
       WHERE  number < 455456
    ), cooked_dates AS (
    SELECT base_date
    ,  10000 * Year(base_date) + 100 * Month(base_date) + Day(base_date) AS date_number
    ,  CASE WHEN 1 = Day(base_date) THEN Convert(CHAR(6), base_date, 112) END AS from_yyyymm
    ,  Convert(CHAR(6), base_date, 112) AS to_yyyymm
    ,  base_date AS when_valid
    ,  DateAdd(day, 1, base_date) AS when_invalid
    ,  Year(base_date) AS calendar_year
    ,  Month(base_date) AS calendar_month
    ,  Day(base_date) AS calendar_day
    ,  CASE WHEN Month(base_date) > 9 THEN  1 ELSE 0 END + Year(base_date) AS federal_year
    ,  CASE WHEN Month(base_date) > 9 THEN -9 ELSE 3 END + Month(base_date) AS federal_month
    ,  Day(base_date) AS federal_day
    ,  DateName(month, base_date) AS month_name
    ,  DateName(dw, base_date) AS day_of_week
    ,  Left(DateName(dw, base_date), 3) AS dow
         FROM raw_dates
    )
    SELECT *
       FROM cooked_dates
    By indexing the table on the when_valid and when_invalid columns, you can easily hunt down the appropriate row for any temporal expression (not just Getdate(), but any date, datetime, smalldatetime, datetime2, etc.) using something like:
    Code:
    SELECT *
       FROM myDateTable
       WHERE  when_valid <= GetDate() 
          AND GetDate() < when_invalid
    When you convert your data to the YYYYMM, you can simply use the to_yyyymm column and you'll get the correct answer. When you must convert back, use the from_yyyymm column (this handles the asymmetry in the date format because of the CASE expression).

    Date tables are your friends!

    Oh yeah, I threw in the federal values just to make life simpler for folks who need a fiscal calendar. Since many fiscal calendars match the United States Government and their fiscal year starts on October 1, that's the example that I used.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat thanks for the scripts I appreciate it.

    Hey do you mind assisting with this. Please review.

    So I have a data warehouse table I am going to that has the date like this YYYYMM @ VARCHAR. I am trying to cast or convert this thing so SSMS knows its a data so I can do an insert into a date field. Any idea how to get that to play nicely. I am trying to convert and cast and I am getting nowhere in a hurry.

    Reviewing your SQL now I believe you have the answer in there. Sorry.....

    Disregard I am making heads and tails of this now. Too much coffee and ADHD.
    Last edited by VLOOKUP; 08-24-15 at 12:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •