problem with date format,need help please!

[#1]

hi,

I have a field coming from  a csv file to my script having data in below format

1/14/2015  12:00:00 AM

I want to change this by removing the time stamp from it and make it to result date in

dd/mm/yyyy format.

how can this be done?? please can someone help

sahana

[simenkg · Accepted solution]

try Date(Date#(subfield(Field,' ',1),'MM/DD/YYYY'),'DD/MM/YYYY')

[MK_QSL]

Date(Floor(Timestamp#([Time Stamp],'M/D/YYYY h:mm:ss TT'))) as Date

[jagan]

Hi,

Try like this

Data:

LOAD

*,

Date(Date#(subfield(DateFieldName,' ',1),'M/D/YYYY'),'DD/MM/YYYY') AS FormattedDate

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[#5]

thanks a lot all of you  for your valuable help.

Re: problem with date format,need help please!

Simen/Jagan

can you please explain the logic here how its working,as I was unable to understand.

[PrashantSangle]

Hi,

Try this,

Date(Date#(Subfield(dateFieldName,' ',1),'MM/DD/YYYY'),DD/MM/YYYY)

Regards

[jagan]

Hi Sahana,

Please find below explanation

subfield(DateFieldName,' ',1) - Returns only Date part

Date#(subfield(DateFieldName,' ',1),'M/D/YYYY') - Converts string date to default date format

Date() - Will format the date into required date format

Hope this helps you.

Regards,

Jagan.

[simenkg]

The subfield(Text,'Delimiter', Field number) splits the Text-string into smaller text strings. It splits them where the delimiter is. In your case the delimiter is a space, hence the ' ' . Since we want the text part that is in front of the space the Field number becomes 1. So subfield('01/14/2014 12:00:00: AM',' ',1) returns '01/14/2014'. Then we use the Date#(Text, Format) function that tells qlikview that it is supposed to read the text as a date with the specified format. So Date#('01/14/2014','MM/DD/YYYY') gives us a date field with the value 14th of january 2014. Then we use the Date(DateField, Format) function. The Date function tells qlikview to change the format of the DateField into the specified Format. So in our case Date(01/14/2014,'DD/MM/YYYY') returns a datefield with format DD/MM/YY or 14/01/2014.

Hope that clears it up.

Regards

SKG