Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am attempting to change the date format of a date in a .csv file downloaded from format: 01-Dec-2022 08:00 to 01/12/2022 08:00 (DD/MM/YYYY HH:MM)
I have tried a few different conversations but cannot convert - to /
Below is the script I am using in Qlikview:
LOAD
date#(date, 'DD/MM/YYYY HH:MM') as [Date],
rain,
temp,
wetb,
dewpt,
vappr,
rhum,
msl as 'Mean Sea Level Pressure hPa',
wdsp,
wddir
FROM
[https://cli.fusio.net/cli/climate_data/webdata/hly1875.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 17 lines);
Try
TimeStamp(TimeStamp#(date, 'DD-MMM-YYYY hh:mm'),'DD/MM/YYYY hh:mm') as TimeStamp,
Date(Floor(TimeStamp#(date, 'DD-MMM-YYYY hh:mm')),'DD/MM/YYYY') as Date,
See also https://community.qlik.com/t5/Design/The-Date-Function/ba-p/1463157
Try
TimeStamp(TimeStamp#(date, 'DD-MMM-YYYY hh:mm'),'DD/MM/YYYY hh:mm') as TimeStamp,
Date(Floor(TimeStamp#(date, 'DD-MMM-YYYY hh:mm')),'DD/MM/YYYY') as Date,
See also https://community.qlik.com/t5/Design/The-Date-Function/ba-p/1463157
That is exactly what I needed. Thanks a lot for the help!
Hi
Try this
timestamp#(date(date#(subfield('01-Dec-2022 08:00',' '),'DD-MMM-YYYY'),'DD/MM/YYYY')&' '&Time(time#(right('01-Dec-2022 08:00',5),'hh:mm'),'hh:mm'),'DD/MM/YYYY HH:MM')
should give :
01/12/2022 08:00
Instead, this is due to the way Excel and other spreadsheet programs open a CSV file and display the data therein. Basically, spreadsheet programs are designed to be used for calculation purposes, so they tend to apply mathematical formats to numbers when the CSV file is opened directly into the program.
Regards,
Diana