Get Date from DateTime field

[sushmitha]

I have a Datetime field in my table in format m/dd/yyyy hh:mm:ss AM
How to get only date from this datetime field ??

I need to display the field only for Date like 'MM/DD/YYYY'

Thanks for your help

 

[namliam]

Either you need to set the "format" to "mm/dd/yyyy" or try Trunc(yourfield) to trim the time off.

 

[Pat Hartman]

Here comes that Oracle advice again Trunc() isn't a VBA or SQL function and truncation only works with text strings. It doesn't work with numeric values (which is how dates are stored).

To extract the date and keep it as a date data type, use DateValue(yourdate) or TimeValue(yourdate) to get the time. To format the date as a string, use Format(yourdate, "mm/dd/yyyy")

 

[namliam]

Your right Pat, sorry... Frigging Oracle... I am beeing totaly oracle-ised ...

Tho in Oracle trunc does most definatly work on date/time fields to be trunced to a date only.

 

[Pat Hartman]

It sounds like Trunc() removes the decimal part of a number.

 

[raskew]

Pat -

Apparently it's stranger than that. See:

http://www.techonthenet.com/oracle/functions/trunc_date.php

Bob

 

[Pat Hartman]

You're right. It is truely a strange function. I don't even get what "day" is doing.