Format DATETIME to Date in query on linked table
in [Queries]
|
From: cjon on 6 May 2010 12:44 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
From: raskew via AccessMonster.com on 6 May 2010 12:59 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/Forums.aspx/access-queries/201005/1
From: John Spencer on 6 May 2010 15:13 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
From: KARL DEWEY on 6 May 2010 15:23 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/Forums.aspx/access-queries/201005/1 > > . >
From: John W. Vinson on 6 May 2010 15:43
On Thu, 6 May 2010 12:23:01 -0700, KARL DEWEY <KARLDEWEY(a)discussions.microsoft.com> wrote: >Why not just DateValue(Now()) ? Or even simpler Date()? -- John W. Vinson [MVP] |