12 Replies Latest reply: Mar 31, 2013 11:31 AM by Christoph Gerber RSS

    Problems with Dateformat and matching date formats

      Hey everyone,

       

      i have a problem with matching my data from two different sources:

       

      1. mysql Database with the date format: "30.11.2011 17:23:42" ->  "DD.MM.YYYY hh:mm:ss"

      2. Google Adwords with the date format: "20111130" -> "YYYYMMDD"

       

      My goal is to have a matching "date" field and the possibilty to select Year, Month and week.

       

      1. Mysql: when i add

       

      year(date_added) as year,

      month(date_added) as month,

      week(date_added) as week

       

      to my query i get in return:

       

      Year -> proper years like 2011,2012 etc.

      Month -> displayed as 1,2,3...etc but not as march, may, june etc.

      Week -> displayed as 1,2,3

       

      I still nee a query which kills the timestamp for me so i only get the date DD.MM.YYYY

       

      2. Google Adwords: i added the code

       

      dim_date as dim_date,

      makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date,

       

      this returns the date in the wished format: DD.MM.YYYY

       

      But then wenn i try to get the pairing with the other data i get funny stuff coming out

       

      month(dim_date) as month,

      week(dim_date)as week,

      year(dim_date) as year

       

      Year -> strange number such as 57014

      Month ->in contrary to the mysql query it returns in May, june, july...

      Week -> displayed as 1,2,3

       

      When i select the date from the adwords data like 01.01.2013 it gives me as corresponding month may and some random year. See screenshot attached.