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.