Dates issue

[alec1982]

Hello,

On a date columns I am getting values as 01/01/2010 and July/10/2011 and 10/July/2012

Meaning some of the values are text and some are numbers.. what would be the best way to format the date in this case?

Thxs.

[Anonymous]

Try Date(Field,'dd-mm-yyyy') as Date

Let me know if this works

[#3]

Alec

I recommand you use QV help and search in String and Dates functions like

left, mid, right,

makedate, month, year...

check applymap as well to transform jan as 1, feb as 2...july as 7 and so on

best regards

Chris

[Clever_Anjos]

maybe you can use an "alt" chain to try to convert your dates

alt(

     date#(yourfield,'DD/MMM/YYYY'),

     date#(yourfield,'MMM/DD/YYYY'),

     date#(yourfield,'DD/DD/YYYY')

)

[Anonymous]

How about this:

//   your original example data
data:
load * inline [
date
01/01/2010
July/10/2011
10/July/2012];

//   map out the month names to numbers
mapmonth:
mapping load * inline [
month, num
January, 1
February, 2
March, 3
April, 4
May, 5
June, 6
July, 7
August, 8
September, 9
October, 10
November, 11
December, 12];

//   format the date for the three scenarios given.
data2:
load date,
if(isnum(date),date(date),
if(isnum(subfield(date,'/',1)),makedate(right(date,4),applymap('mapmonth',subfield(date,'/',2)),subfield(date,'/',1)),
makedate(right(date,4),applymap('mapmonth',subfield(date,'/',1)),subfield(date,'/',2)))) as dateformatted,
applymap('mapmonth',subfield(date,'/',2))
resident data;

[MK_QSL · Accepted solution]

Try below in your script...

DateTable:

Load

  DateField,

  DATE(ALT(Date#(DateField,'DD/MM/YYYY'),

  Date#(DateField,'MMMM/DD/YYYY'),

  Date#(DateField,'DD/MMMM/YYYY'))) as DateFieldChanged

Inline

[

  DateField

  01/01/2010

  July/10/2011

  20/July/2011

];

[Anonymous]

That is quite elegant, Manish!

[alec1982]

Thank you!