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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|