topic Re: Timestamp extraction in QlikView

Timestamp extraction

trishita — Wed, 25 Nov 2020 16:16:04 GMT

How to extract timestamp from a date field of format yy-mm-dd hh:mm to have a result only yy-mm-dd

Re: Timestamp extraction

YoussefBelloum — Thu, 03 May 2018 10:14:59 GMT

so here you want to extract a date from a timestamp ?

try this:

=Date(Floor(your_field))

Re: Timestamp extraction

bhaskar_sm — Thu, 03 May 2018 10:39:33 GMT

Hi,

You can use Date function with floor and Num function.

Date(floor(num(TimeStampField)),'YY-MM-DD') as Date

Regards,

Bhasker Kumar

Re: Timestamp extraction

jonathandienst — Thu, 03 May 2018 10:46:50 GMT

If the field is text, then this will interpret and convert

Date(Floor(Timestamp#(DateField, 'yy-MM-dd hh:mm'), 'yy-MM-dd')

Re: Timestamp extraction

sasiparupudi1 — Thu, 03 May 2018 11:34:36 GMT

Date(Floor(Timestamp#(YourDate,'YY-MM-DD hh:mm')),'YY-MM-DD')

Re: Timestamp extraction

trishita — Thu, 03 May 2018 11:45:46 GMT

LOAD

BUNKER_DELIVERY_NOTE_NUMBER,
DENSITY,
DIFFERENTIAL_REASON,
FUEL_MASS_AFTER_SOUNDING,
FUEL_MASS_BEFORE_SOUNDING,
FUEL_TYPE,
IMO_NUMBER,
LOWER_HEATING_VALUE,
SOUNDING_TIME_TIMEZONE,
SOUNDING_TIME_UTC,
SULPHUR_CONTENT,
TEMPERATURE,
VISCOSITY,
WATER_CONTENT
FROM
mo_purch_sounding_correction_view.qvd

(qvd);

SOUNDING_TIME_UTC in a view is of format yy-mm-dd hh:mm .I want to extract out the hh:mm and have result in form of only YY-MM-DD:

The field SOUNDING_TIME_UTC is in the form of vsql_varchar

Re: Timestamp extraction

trishita — Thu, 03 May 2018 11:46:05 GMT

SOUNDING_TIME_UTC in a view is of format yy-mm-dd hh:mm .I want to extract out the hh:mm and have result in form of only YY-MM-DD:

The field SOUNDING_TIME_UTC is in the form of vsql_varchar

Re: Timestamp extraction

sasiparupudi1 — Thu, 03 May 2018 11:50:12 GMT

LOAD

BUNKER_DELIVERY_NOTE_NUMBER,
DENSITY,
DIFFERENTIAL_REASON,
FUEL_MASS_AFTER_SOUNDING,
FUEL_MASS_BEFORE_SOUNDING,
FUEL_TYPE,
IMO_NUMBER,
LOWER_HEATING_VALUE,
SOUNDING_TIME_TIMEZONE,
Date(Floor(Timestamp#(SOUNDING_TIME_UTC,'YY-MM-DD hh:mm')),'YY-MM-DD') AS SOUNDING_DATE,
SULPHUR_CONTENT,
TEMPERATURE,
VISCOSITY,
WATER_CONTENT
FROM
mo_purch_sounding_correction_view.qvd

(qvd);

Re: Timestamp extraction

bhaskar_sm — Thu, 03 May 2018 11:50:16 GMT

SubField(SOUNDING_TIME_UTC ,' ',1) as Date

Re: Timestamp extraction

vishalarote — Thu, 03 May 2018 12:35:38 GMT

Try this

😧

LOAD * INLINE [

Date

10-01-15 10:45

];

load

Date(Date#(date(floor(Timestamp#(Date,'yy-mm-dd hh:mm'))),'dd-mm-yyyy'),'yy-mm-dd') as NDate

Resident D;