Format DATETIME to Date in query on linked table

[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

 

[raskew via AccessMonster.com]

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

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

 

[John Spencer]

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

 

[KARL DEWEY]

Why not just DateValue(Now()) ?

--
Build a little, test a little.

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

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

--



.

 

[John W. Vinson]

Why not just DateValue(Now()) ?

Or even simpler Date()?

 

[cjon]

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

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

.