Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to convert date& time format(3/2/2015 7:00:00 AM) to Date(3/2/2015) while linking(ODBC) the tables from MSACCESS DB.
try in qlik load
date(floor(<your timestamp field>), 'MM/DD/YYYY') as Date
or
try in source load
FROM_UNIXTIME(<your timestamp field>,"%c/%d/%Y") as Date
You can use the Floor function to cut off the time part and the Date function to change the display format. Something like:
LOAD Date(Floor(MyDate),'M/D/YYYY') as MyDate, .... ;
SQL Select MyDate, ... From ...
Hi,
You need to use Timestamp#() and Date() functions to transform the field into date and time field.
Ex: Date(Timestamp#(<DB Fieldname>, 'M/D/YYYY h:mm:ss tt'), 'M/D/YYYY') As <Date Field>
Date(Timestamp#(<DB Fieldname>, 'M/D/YYYY h:mm:ss tt'), 'h:mm:ss tt') As <Time Field>
Thanks, But results came as date followed by time and followed by date. I need date only.
Query
ODBC CONNECT32 TO [MS Access Database;DBQ=.....\Tracker\database1.accdb];
A:
SQL SELECT *
FROM Qualifying;
It's sometimes easier to truncate the time portion before hitting the QlikView functions and syntax (in the case of preceding loads).
Instead of using "SELECT *", try naming each column explicitly and using the SQL "Cast" function. It was part of SQL-92, so most ODBC drivers implement it by now (though not always):
SELECT Cast(AssignedDate as Date) as AssignedDate
Hi,
your screenshot shows, that you loaded A.AssignedDate as Timestamp and added an expression in a Listbox, that additionally shows the date part of the timestamp.
That's why you are ending up with "date followed by time and followed by date".
Try to extract the date part already in the load script like Gysbert suggested.
Instead of
Date(Floor( ...
you could also try with
DayName(A.AssignedDate) as A.AssignedDate
if you have set your default DateFormat accordingly.
hope this helps
regards
Marco