Dates not being formatted

[stascher]

Hello,

I have a table of strings of the following format:

'2017-05-16'

'2018-04-27'

I need to convert them to real date values. When I use date#([input_date],'YYYY-MM-DD')  as [output_date], they appear in my Table objects as numbers:

42871

43217

When I try to re-format them with date(date#([input_date],'YYYY-MM-DD'),'YYYY-MM-DD') as [output_date]

they appear in my Table object as question marks:

?

?

Yes, I can fix this by putting date([output_date],'YYYY-MM-DD') in my Table columns, but why should I have to? The use of date#() or date(date#()) as above should accomplish what I need without adding date() to every column in my Tables. Am I doing something wrong? 

Regards,

Steven

[Chanty4u]

Try this.

Date(Date#(PurgeChar([input_date],' '),'YYYY-MM-DD'),'YYYY-MM-DD') as [output_date]

[BrunPierre]

I’d look at two things. First, there might be trailing spaces or invisible characters in the source data. Try cleaning it like this:

Date(Date#(Trim([input_date]), 'YYYY-MM-DD'), 'YYYY-MM-DD') as [output_date]

Second, make sure your global date format matches the data by setting:

SET DateFormat='YYYY-MM-DD';

[Amit_Prajapati]

Hi @stascher  ,

I've encountered similar issues when working with date fields—especially when trailing spaces or invisible characters in the source data cause unexpected behavior, as @BrunPierre  also pointed out.

To resolve this, it's best to apply trimming  or with PurgeChar and enforce the required format explicitly:

date(Date#(Trim([input_date]), 'YYYY-MM-DD'), 'YYYY-MM-DD')