Problems with Dateformat and matching date formats

[#1]

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.

[somenathroy]

Hi,

You have to format Extracted Date from sources with Date() and Date#() functions.

1. For MySql:

Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,

Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,

Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week,

2. For Google Adwords:

Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')  as date,

Year(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY'))  as year,

Month(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY') ) as month,

Week(Date(Date#(dim_date,'YYYYMMDD'),'DD.MM.YYYY')) as week

Regards,

som

[#3]

Hey,

the adwords part worked like a charm. With the other data i am having problems:

this is the query i am using:

------

SELECT

     date_added as date_added,

     Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,

     Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

     Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,

     Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week

from mysql_database.orders

----

i get this Script error:

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.2(a) Driver][mysqld-5.1.66-0+squeeze1-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,

Mont' at line 5

Can you help?

[Anonymous · Accepted solution]

Chris,

You cannot use QlikView functions in SQL, it doesn't understand this.  Use preceeding load, like this:

LOAD
     date_added,

     Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY') as date,
     Year(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as year,
     Month(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as month,
     Week(Date(Date#(Mid(date_added,1,10),'DD.MM.YYYY'),'DD.MM.YYYY')) as week
;
SELECT
date_added
from mysql_database.orders;

Regards,
Michael

[#5]

Hey, thanks alot. Maybe i have fault in my thinking. The database format in the mysql table is:

2012-05-29 11:58:55

@Michael: with your help i dont get the error but i also get no values.

[Anonymous]

If the data format is different, you have to replace this

Date#(Mid(date_added,1,10),'DD.MM.YYYY')

with

Date#(Mid(date_added,1,10),'YYYY-MM-DD')

[#7]

Hey,

tried that. Still doenst return any right data. Any other ideas?

[Anonymous]

sure 🙂

Try this, and tell what it returns, if anything:

LOAD
     date_added
;
SELECT
date_added
from mysql_database.orders;

[#9]

Hey, got it working. I put your the normal format.

week(date_added) as week,

etc..

before the

date_added

from mysql_live:orders;

But now i have one last problem:

Google adwords gives me back Month as March,June etc.

Mysql gives me back 1,2,3 etc.

thats my last question. Thanks so far for helping out!!!

[Anonymous]

That means that the date_added in mySQL is in fact a date field, no need for date#().

Month - can't you use simply month(date_added)?