Regarding SQL datetime

[#1]

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!

[Sokkorn]

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

[Michiel_QV_Fan]

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.

[hic]

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

[#5]

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.

[hic]

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

[#7]

[hic]

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

[#9]

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`

[hic · Accepted solution]

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