A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Format DATETIME to Date in query on linked table



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2010, 05:44 PM posted to microsoft.public.access.queries
cjon
external usenet poster
 
Posts: 2
Default Format DATETIME to Date in query on linked table

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
  #2  
Old May 6th, 2010, 05:59 PM posted to microsoft.public.access.queries
raskew via AccessMonster.com
external usenet poster
 
Posts: 370
Default Format DATETIME to Date in query on linked table

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 wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #3  
Old May 6th, 2010, 08:13 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Format DATETIME to Date in query on linked table

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

cjon wrote:
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

  #4  
Old May 6th, 2010, 08:23 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Format DATETIME to Date in query on linked table

Why not just DateValue(Now()) ?

--
Build a little, test a little.


"raskew via AccessMonster.com" wrote:

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 wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

.

  #5  
Old May 6th, 2010, 08:43 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Format DATETIME to Date in query on linked table

On Thu, 6 May 2010 12:23:01 -0700, KARL DEWEY
wrote:

Why not just DateValue(Now()) ?


Or even simpler Date()?
--

John W. Vinson [MVP]
  #6  
Old May 10th, 2010, 02:34 PM posted to microsoft.public.access.queries
cjon
external usenet poster
 
Posts: 2
Default Format DATETIME to Date in query on linked table

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" wrote:

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

cjon wrote:
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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.