Converting string to date

[#1]

hi,

i am loading data from SQL and the date format is in varchar and is looking like this 16AUG2016 , i am needing to convert it to date but i am batlling,

16AUG2016

26MAR2015

28AUG2015

03MAY2017

26MAR2015

26MAR2015

26MAR2015

09MAR2017

26MAR2015

13JAN2017

26MAR2015

can anybody assist please

regards,

marvin

[prma7799]

Try this

=Date(Date#('16AUG2016','DDMMMYYYY'),'DD-MMM-YYYY')

[antoniotiman]

Hi Marvin

try this

Date(Date#(FieldDate,'DDMMMYYYY'))

Regards,

Antonio

[Chanty4u]

try this script

A:

load * Inline [

Mydate

16AUG2016

26MAR2015

28AUG2015

03MAY2017

26MAR2015

26MAR2015

26MAR2015

09MAR2017

26MAR2015

13JAN2017

26MAR2015

];

result::

load

*,

date(Date#(Mydate,'DDMMMYYYY'),'DD-MM-YYYY') AS Newdate

Resident A;

[#5]

hi,

thanks for response , when i run script in the load statement it gives

20APR1970:00:00:00

20APR1979:00:00:00

20APR1980:00:00:00

[#6]

hi,

thanks i loaded i still get the same result

20APR1970:00:00:00

[Chanty4u]

try same change below

load

*,

date(Date#(Mydate,'DDMMMYYYY  hh:mm:ss'),'DD-MM-YYYY') AS Newdate

Resident A;

[prma7799]

Can you please share some sample data?

[#9]

PIS_DATE

16AUG2016:09:55:01

26MAR2015:10:56:34

28AUG2015:12:37:40

03MAY2017:13:03:52

26MAR2015:09:28:32

26MAR2015:09:31:16

26MAR2015:09:32:06

09MAR2017:16:16:54

26MAR2015:09:33:33

13JAN2017:14:03:30

26MAR2015:09:40:45

26MAR2015:09:44:51

16APR2017:21:16:43

16JAN2017:08:29:20

26MAR2015:09:57:55

29JUL2015:00:31:05

26MAR2015:09:59:55

26APR2017:22:57:52

29JAN2016:07:25:20

20MAR2017:22:59:19

i have been try in sql to convert it but it seems this a in varchar

[Chanty4u]

Did you tried this?