Format DATETIME to Date in query on linked table

  • Thread starter Thread starter cjon
  • Start date Start date
C

cjon

Access 2007 hitting an Oracle Database with MS ODBC Drivers for Oracle V 10.x

I am pulling data from a linked Oracle table that contains a datetime field,
[ENTRY_DATETIME]. It is one of the fields I pull as part of a make table
query. I would like the data written to the new table (Step_1) to be a Date
field with the short date format. Is there a way to format the data I write
to the new table as a date?

This: "Select..., Format([XXXX_XXXX.ENTRY_DATETIME],"mm/dd/yyyy") AS
ENTRY_DATETIME ... into Step_1 from ....."

Exports it as text.

I'm pretty new at this. Thanks for your help.
CJon
 
Short Date is a format that controls the DISPLAY of the data in a DateTime field.

A datetime field stores the date and time as a number (?special case of a
double?) where the integer portion represents the number of days from Dec 31,
1899 and the decimal portion represents the fractional portion of 24 hours.

If you are trying to strip the time out of the Entry_DateTime field and store
ONLY the date portion, you can use
DateValue([ENTRY_DATETIME]) as long as every entry in the field has a date
(no nulls)

Otherwise, you can test first with the IsDate function and then return nulls
for values that cannot be converted by the DateValue function
IIF(IsDate([ENTRY_DATETIME]),DateValue([ENTRY_DATETIME]),Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Why not just DateValue(Now()) ?

--
Build a little, test a little.


raskew via AccessMonster.com said:
Hi -

Use the cStr() and DateValue() functions. Here's an example of a date/time
returned as a string:

x = cstr(now())
? x
5/6/2010 11:55:15 AM

To convert this as a short/date:
? DateValue(x)
5/6/2010

HTH - Bob
cjon said:
Access 2007 hitting an Oracle Database with MS ODBC Drivers for Oracle V 10.x

I am pulling data from a linked Oracle table that contains a datetime field,
[ENTRY_DATETIME]. It is one of the fields I pull as part of a make table
query. I would like the data written to the new table (Step_1) to be a Date
field with the short date format. Is there a way to format the data I write
to the new table as a date?

This: "Select..., Format([XXXX_XXXX.ENTRY_DATETIME],"mm/dd/yyyy") AS
ENTRY_DATETIME ... into Step_1 from ....."

Exports it as text.

I'm pretty new at this. Thanks for your help.
CJon
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Since [ENTRY_DATETIME] is automatically inserted, it exists in every record.
Thus, DateValue([ENTRY_DATETIME]) was the cleanest and most direct way, and
it worked like a champ. Many Thanks to John Spencer and all who responded.

CJon

John Spencer said:
Short Date is a format that controls the DISPLAY of the data in a DateTime field.

A datetime field stores the date and time as a number (?special case of a
double?) where the integer portion represents the number of days from Dec 31,
1899 and the decimal portion represents the fractional portion of 24 hours.

If you are trying to strip the time out of the Entry_DateTime field and store
ONLY the date portion, you can use
DateValue([ENTRY_DATETIME]) as long as every entry in the field has a date
(no nulls)

Otherwise, you can test first with the IsDate function and then return nulls
for values that cannot be converted by the DateValue function
IIF(IsDate([ENTRY_DATETIME]),DateValue([ENTRY_DATETIME]),Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Access 2007 hitting an Oracle Database with MS ODBC Drivers for Oracle V 10.x

I am pulling data from a linked Oracle table that contains a datetime field,
[ENTRY_DATETIME]. It is one of the fields I pull as part of a make table
query. I would like the data written to the new table (Step_1) to be a Date
field with the short date format. Is there a way to format the data I write
to the new table as a date?

This: "Select..., Format([XXXX_XXXX.ENTRY_DATETIME],"mm/dd/yyyy") AS
ENTRY_DATETIME ... into Step_1 from ....."

Exports it as text.

I'm pretty new at this. Thanks for your help.
CJon
Click to expand...
.
Click to expand...
 
Back
Top