topic Date and time to Date format from access in QlikView

Date and time to Date format from access

Thu, 22 Oct 2015 17:33:54 GMT

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.

Re: Date and time to Date format from access

santhoo_san — Thu, 22 Oct 2015 17:50:42 GMT

try in qlik load

date(floor(<your timestamp field>), 'MM/DD/YYYY') as Date

try in source load

FROM_UNIXTIME(<your timestamp field>,"%c/%d/%Y") as Date

Re: Date and time to Date format from access

Gysbert_Wassenaar — Thu, 22 Oct 2015 17:52:07 GMT

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 ...

Re: Date and time to Date format from access

srchilukoori — Thu, 22 Oct 2015 17:52:33 GMT

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>

Re: Date and time to Date format from access

Thu, 22 Oct 2015 18:39:28 GMT

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];

SQL SELECT *

FROM Qualifying;

Re: Date and time to Date format from access

Anonymous — Thu, 22 Oct 2015 19:28:05 GMT

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

Re: Date and time to Date format from access

MarcoWedel — Thu, 22 Oct 2015 21:19:13 GMT

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