Date Formatting

building reports webintelligence
1

Hello,

I am trying to convert a date that is in a string format to a date format.

The field I am pulling is in the format:
16-01-2010 00:00:00

I want it to be displayed in a date format so that I can do a count on the number of days between two dates. The format I would like it to display in is:

mm/dd/yyyy
09/09/2009

Does anyone know how to do this?

Thanks for your help,

Mike

Mike Giannou :canada: (BOB member since 2009-09-08)

2

Try this.

=FormatDate(ToDate([Date];"dd/MM/yyyy hh:mm:ss");"mm/dd/yyyy")

Jansi :india: (BOB member since 2008-05-12)

3

You might want:


=FormatDate(ToDate([Date];"dd/MM/yyyy hh:mm:ss");"MM/dd/yyyy")

with the last MMs in capitals, otherwise you might get the minutes instead, which would be all zeros since you don’t have a time in your database value.

Throws me every time. I never get a date conversion right first time!

Debbie

Debbie :uk: (BOB member since 2005-03-01)

4

:oops: I didn’t take much care I took for the first format.

Jansi :india: (BOB member since 2008-05-12)

5

Works like a charm…Thank You :smiley:

Mike Giannou :canada: (BOB member since 2009-09-08)