topic Re: Regarding SQL datetime in QlikView

Regarding SQL datetime

Fri, 14 Feb 2014 08:11:22 GMT

Hi!

I have connect my Qlikview to a ODBC SQL server.

Example:

SQL SELECT `date_hi`,

FROM aabridge.`Call_hi`

Then i get all the dates. The format of the dates are in "Datetime" so. "YYYY-MM-DD HH-MM-SS".

Now i want to make a select so i get the years and the month, and that should be easy so i try:

SQL SELECT datepart(yyyy, `date_hi`) AS Year,

datepart(mm, `date_hi`) as Month,

FROM aabridge.`Call_hi`

But then i get SQL error when i try to load saying:

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.1 Driver][mysqld-4.1.20]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 '(yyyy, `date_hi`) AS Year,

datepart(mm, `date_hi`) as Month,

FROM aabridge' at line 1

SQL SELECT datepart(yyyy, `date_hi`) AS Year,

datepart(mm, `date_hi`) as Month,

FROM aabridge.`Call_hi`

Can someone please help me?

Thank you!

Re: Regarding SQL datetime

Sokkorn — Fri, 14 Feb 2014 08:19:59 GMT

Hi Erik,

Do this way

[TableName]:

Load

date_hi AS OrigDate,

Date(Floor(date_hi)) AS NewDate,

Month(date_hi)) AS NewMonth,

Year(date_hi)) AS NewYear;

SQL SELECT date_hi,

FROM aabridge.Call_hi

Regards,

Sokkorn

Re: Regarding SQL datetime

Michiel_QV_Fan — Fri, 14 Feb 2014 08:21:02 GMT

Load the date and perform the transformation in your load script:

Load Year(ate_hi) as Year,

Month(date_hi) as Month,

etc.

sql select ....

And consider using a master calendar to link to your date field.

Re: Regarding SQL datetime

hic — Fri, 14 Feb 2014 08:21:51 GMT

First of all: The SELECT statement is NOT evaluated by QlikView - it is just sent to your ODBC client and is evaluated there. So the syntax for the correct SELECT depends on the ODBC driver.

Secondly, it is better if you let QlikView define Year, Month, Day instead of doing it in the SELECT. E.g. by

Load *,

Year(date_hi) as Year,

Month(date_hi) as Month,

Day(date_hi) as Day

;

SQL SELECT `date_hi`,

FROM aabridge.`Call_hi` ;

Or even better - in a separate table:

Transactions:

SQL SELECT `date_hi`,

FROM aabridge.`Call_hi` ;

Calendar:

Load distinct

date_hi,

Year(date_hi) as Year,

Month(date_hi) as Month,

Day(date_hi) as Day

Resident Calendar;

HIC

Re: Regarding SQL datetime

Fri, 14 Feb 2014 08:35:28 GMT

First of all, thanks to all of you for answering so fast!

Yes, ok.

When i do it that way, loading the Year and the Month then i get no relations between the different listboxes?

When i choose for example 2013 and Jan the related data in the date_hi dont get highlighted

So the Year and Month are not related in any way which i also can se in the tableview.

Re: Regarding SQL datetime

hic — Fri, 14 Feb 2014 08:40:35 GMT

If they are not related in the table viewer, you have to create this relationship: You need to include a key between your tables in your Load/SELECT statements. Post an image of your table viewer.

HIC

Re: Regarding SQL datetime

Fri, 14 Feb 2014 08:41:48 GMT

Re: Regarding SQL datetime

hic — Fri, 14 Feb 2014 08:54:56 GMT

Instead of

Year(date_hi) as Year,

Month(date_hi) as Month,

you should have

date_hi as date_from,

Year(date_hi) as Year,

Month(date_hi) as Month,

HIC

Re: Regarding SQL datetime

Fri, 14 Feb 2014 09:02:39 GMT

Sorry for the unclear names here.

There is actually no Call_hi or date_hi.

This is my Load and Select, and that generates the tableviewer

LOAD Year(date_from) as Year, Month(date_from) as Month;

SQL SELECT `date_from`

FROM ccbridge.`Call_logg`

Re: Regarding SQL datetime

hic — Fri, 14 Feb 2014 09:07:28 GMT

Then you should do a

LOAD date_from, Year(date_from) as Year, Month(date_from) as Month;

SQL SELECT distinct `date_from` FROM ccbridge.`Call_logg`

HIC

Re: Regarding SQL datetime

Fri, 14 Feb 2014 09:09:43 GMT

I see, that is actually quite beautiful!

Thank you!