Date Conversion

[pkelly]

I have a database with a field called creation date which has the following example data...

09/12/2009 15:41:56

09/12/2009 15:42:28

09/12/2009 15:42:32

09/12/2009 15:43:00

09/12/2009 15:43:09

09/12/2009 15:43:18

08/12/2009 16:04:35

08/12/2009 16:05:33

09/12/2009 09:14:19

09/12/2009 09:17:38

09/12/2009 09:18:39

09/12/2009 09:19:34

09/12/2009 09:19:37

09/12/2009 09:19:42

09/12/2009 09:19:49

09/12/2009 09:20:05

09/12/2009 09:20:09

09/12/2009 09:20:09

09/12/2009 09:20:22

09/12/2009 09:20:27

09/12/2009 09:21:44

09/12/2009 09:26:37

09/12/2009 09:27:51

08/12/2009 13:48:51

08/12/2009 13:51:45

08/12/2009 13:15:42

08/12/2009 15:53:31

08/12/2009 15:54:09

In my script, I have the following...

Date

(creationdate,'DD/MM/YYYY') AS analysisDate

I have added a list box to my report and had hoped to see these group by date...

09/12/2009

08/12/2009

Instead I get...

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

08/12/2009

08/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

09/12/2009

08/12/2009

08/12/2009

08/12/2009

08/12/2009

08/12/2009

I had hoped that formatting it with date would have removed the timestamp with the net result being that my list box would be grouped...

Any suggestions on how I achieve this greatly appreciated....

[#2 · Accepted solution]

Hi,

use the Floor function like as following:

Date(Floor(CreationDate))

Best regards.

[#3]

Hi,

you can alsoo try:

date(left(creationdate,10),'DD/MM/YYYY') as ...

Have Fun

Alex:)

[#4]

could be date#(...)

[pkelly]

Thanks Miguel, this will prove very useful...

[pkelly]

Thanks Alexander - this also does the trick....two for the price of one...

Many Thanks...