Issues converting date from a downloaded .csv

[__dec___]

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

[hic · Accepted solution]

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

[__dec___]

That is exactly what I needed. Thanks a lot for the help!

[brunobertels]

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

[Dianah]

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