Author Topic: date format from Apr 18, 2014 to mmddyyyy  (Read 789 times)

Offline cognos74

  • Full Member
  • ***
  • Join Date: May 2008
  • Posts: 47
  • Forum Citizenship: +0/-0
date format from Apr 18, 2014 to mmddyyyy
« on: 25 Aug 2016 02:13:06 pm »
Hi,

I am getting date in following format in my report "Apr 18, 2014"  so how to get this into 'mmddyyyy'

Thanks,

Offline raj_aries81

  • Community Leader
  • *****
  • Join Date: Jan 2009
  • Posts: 166
  • Forum Citizenship: +0/-0
Re: date format from Apr 18, 2014 to mmddyyyy
« Reply #1 on: 25 Aug 2016 10:53:37 pm »
Goto  "Data Format" option, change the Format Type to Date and set the pattern to MMddYYYY, make sure its not not DD.

Regards
Raj

Offline jody915

  • Associate
  • **
  • Join Date: Oct 2018
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: date format from Apr 18, 2014 to mmddyyyy
« Reply #2 on: 20 Nov 2020 01:11:51 pm »
i have a similar issue where i need the date to be in mmddyyyy format, but the data item is not a column on the report so i do not have the ability to change the Data Format.  What expression would is use to remove the '/' from the date?

Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 263
  • Forum Citizenship: +0/-0
Re: date format from Apr 18, 2014 to mmddyyyy
« Reply #3 on: 20 Nov 2020 01:42:41 pm »
To_date function might work for example:
To_date(’2020-11-20’,’MMddYYYY’)

Offline adam_mc

  • Statesman
  • ******
  • Join Date: Mar 2012
  • Posts: 289
  • Forum Citizenship: +14/-0
Re: date format from Apr 18, 2014 to mmddyyyy
« Reply #4 on: 20 Nov 2020 02:16:31 pm »
If to_date doesn't work, try the extract function.

Shown below with added cast and substring functions to convert to 8-character result with 2-digit day and month values.

substring(cast(extract(month, [Date]) + 900, char(3)), 2, 2) || substring(cast(extract(day, [Date]) + 900, char(3)), 2, 2) || cast(extract(year, [Date]), char(4))

Hope this helps,
Adam.