Date and time to Date format from access

[#1]

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.

[santhoo_san]

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

[Gysbert_Wassenaar]

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

[srchilukoori]

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>

[#5]

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;

[Anonymous]

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

[MarcoWedel]

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