Date and time problems

[TomR100]

Hello,
I am creating a report from a SQL database where there is always an appointment date but not always an appointment time. The field I get this information from is called AppointmentDateTime and it cannot be spilt into Appointment Date and AppointmentTime.

Can someone help me with this formula to have the output 4/26 at 10:00AM or 4/26. Right now if I do not have a time I get no date.

Thank you in advance

 

[synapsevampire]

I'll assume you mean SQL Server and that the data is stored as a datetime.

This should work as a formula:

if time({Testdate.Test}) > time(0,0,0)
then
totext(datepart("m",{Testdate.Test})) + "/" +
totext(datepart("d",{Testdate.Test})) +
" at " + totext(time({Testdate.Test}))
else
totext(datepart("m",{Testdate.Test})) + "/" +
totext(datepart("d",{Testdate.Test}))

Note that you cannot group/sort properly on this formula because it's text.

There are other ways of doing it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[TomR100]

synapsevampire,
The date is coming out like this:

5.00/13.00 at 6:00:00AM
Is there a way in Crystal to format this to come out as
05/13 at 6:00AM or if no time as 05/13

Thank you for your help

 

[Naith]

To get rid of the trailing '00', in your totext strings, end them with "00" just before you close the brackets.

i.e. totext({@time},"00&quot

<N>

 

[synapsevampire]

I think Naith resolved the formatting concern, and the formula I supplied should resolve the date and time or just date if there isn't a time.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[TomR100]

Thank you both very much for your help.