Query Date/Time Field
Dana M. Epp -- eppdm@uniserve.com
Tuesday, January 28, 1997
Environment: NT 4.0, Win95 VC++ 4.2b
Within an Access database, I need to be able to Query a Log Table for all
entries where the Date is equal to today. Could someone explain how I would
set up my Query for this with regards to a CRecordSet.
I set the log with a CTime::GetCurrentTime();, and wonder just how I format
the filter for this in extracting the queried data.
I would need to do something with the m_strFilter such as "Date = ???", but
what is the required format? Should I be converting it using the
CTime.Format() method, or do I require the conversion to a TIMESTAMP_STRUCT?
Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would be
to late in the query process to be of any use.
The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
00:00:00}", however, this fails under my tests. Exactly how would I be able
to query this table for all data on X date?
Any help would be greatly appreciated.
PC'ing you,
Dana M. Epp
eppdm@uniserve.com
http://bedrock.cyberhq.com/dana
"How can one work with the technology of today, using yesterdays
software and methods, and still be on the leading edge tomorrow?
Why settle for less... I won't! "
Ray Barley -- barley@rdaconsultants.com
Wednesday, January 29, 1997
[Mini-digest: 7 responses]
If you know that you're using Access, you could set the CRecordset
m_strFilter to be
"BETWEEN #MM/DD/YYYY 00:00AM# AND #MM/DD/YYYY 11:59:59PM#"
I did exactly this in a time reporting application and it worked okay.
Again this syntax is Access specific so be careful. The generic
solution can probably be found in the ODBC Programmer's Reference and
SDK Guide, Appendix F.
>----------
>From: Dana M. Epp[SMTP:eppdm@uniserve.com]
>Sent: Tuesday, January 28, 1997 6:28 PM
>To: mfc-l@netcom.com
>Subject: Query Date/Time Field
>
>Environment: NT 4.0, Win95 VC++ 4.2b
>
>Within an Access database, I need to be able to Query a Log Table for all
>entries where the Date is equal to today. Could someone explain how I would
>set up my Query for this with regards to a CRecordSet.
>
>I set the log with a CTime::GetCurrentTime();, and wonder just how I format
>the filter for this in extracting the queried data.
>
>I would need to do something with the m_strFilter such as "Date = ???", but
>what is the required format? Should I be converting it using the
>CTime.Format() method, or do I require the conversion to a TIMESTAMP_STRUCT?
>
>Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would be
>to late in the query process to be of any use.
>
>The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
>00:00:00}", however, this fails under my tests. Exactly how would I be able
>to query this table for all data on X date?
>
>Any help would be greatly appreciated.
>
>
>
>
>
>PC'ing you,
>Dana M. Epp
>eppdm@uniserve.com
>http://bedrock.cyberhq.com/dana
>
> "How can one work with the technology of today, using yesterdays
> software and methods, and still be on the leading edge tomorrow?
> Why settle for less... I won't! "
>
>
-----From: ybriklin@juno.com (Yuriy Briklin)
Hi Dana.
You should use the following format:
m_strFilter = "Date = #1-29-97#";
in order to filter by Date.
Yuriy Briklin
e-mail: ybriklin@juno.com
On Tue, 28 Jan 1997 15:28:53 -0800 "Dana M. Epp"
writes:
> Environment: NT 4.0, Win95 VC++ 4.2b
>
> Within an Access database, I need to be able to Query a Log Table for
> all
> entries where the Date is equal to today. Could someone explain how I
> would
> set up my Query for this with regards to a CRecordSet.
>
> I set the log with a CTime::GetCurrentTime();, and wonder just how I
> format
> the filter for this in extracting the queried data.
>
> I would need to do something with the m_strFilter such as "Date =
> ???", but
> what is the required format? Should I be converting it using the
> CTime.Format() method, or do I require the conversion to a
> TIMESTAMP_STRUCT?
>
> Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use
> would be
> to late in the query process to be of any use.
>
> The sample app odbcdate.exe from MS uses the format "Date >= {ts
> YY/MM/DD
> 00:00:00}", however, this fails under my tests. Exactly how would I
> be able
> to query this table for all data on X date?
>
> Any help would be greatly appreciated.
>
>
>
>
>
> PC'ing you,
> Dana M. Epp
> eppdm@uniserve.com
> http://bedrock.cyberhq.com/dana
>
> "How can one work with the technology of today, using yesterdays
> software and methods, and still be on the leading edge
> tomorrow?
> Why settle for less... I won't! "
>
>
-----From: David Little
Here is an example from one of my programs:
CString time;
CTime t =3D CTime::GetCurrentTime();
time =3D t.Format("%Y-%m-%d");
s.Format("(Date between {ts '%s 00:00:00'} and {ts '%s 23:59:59'}) AND =
"
"Terminal_Number =3D '%02d'",
time,time,atoi(termTable->GetCurrentTerminalID()));
remitMaster->m_strFilter =3D s;
You must use the 'BETWEEN' operator. If you say Date =3D {YY/MM/DD =
00:00:00}, you will only retrieve records whose time was set at exactly =
midnite. Oh, and Date is a field in the remitMaster table....
Hope this helps...
----------
From: Dana M. Epp[SMTP:eppdm@uniserve.com]
Sent: Tuesday, January 28, 1997 5:28 PM
To: mfc-l@netcom.com
Subject: Query Date/Time Field
Environment: NT 4.0, Win95 VC++ 4.2b
Within an Access database, I need to be able to Query a Log Table for =
all
entries where the Date is equal to today. Could someone explain how I =
would
set up my Query for this with regards to a CRecordSet.
I set the log with a CTime::GetCurrentTime();, and wonder just how I =
format
the filter for this in extracting the queried data.
I would need to do something with the m_strFilter such as "Date =3D =
???", but
what is the required format? Should I be converting it using the
CTime.Format() method, or do I require the conversion to a =
TIMESTAMP_STRUCT?
Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would =
be
to late in the query process to be of any use.
The sample app odbcdate.exe from MS uses the format "Date >=3D {ts =
YY/MM/DD
00:00:00}", however, this fails under my tests. Exactly how would I be =
able
to query this table for all data on X date?
Any help would be greatly appreciated.
PC'ing you, =20
Dana M. Epp =20
eppdm@uniserve.com
http://bedrock.cyberhq.com/dana
"How can one work with the technology of today, using yesterdays
software and methods, and still be on the leading edge tomorrow?
Why settle for less... I won't! "
-----From: "Nayab Khan"
Dana:
Since Access date fields stores dates as type double the fraction portion
has the time information while the integer portion carries the day. Hence
34567.00001 to 34567.99999 would be a range for one day. Therefore to
filter out a single day for e.g. 1/1/97 use:
"Where ( (fldDate >= #1/1/97#) AND (fldDate < #1/2/97#) )"
Note you must use US dates and also note that the date is padded with #s.
----------
> From: Dana M. Epp
> To: mfc-l@netcom.com
> Subject: Query Date/Time Field
> Date: Tuesday, January 28, 1997 5:28 PM
>
> Environment: NT 4.0, Win95 VC++ 4.2b
>
> Within an Access database, I need to be able to Query a Log Table for all
> entries where the Date is equal to today. Could someone explain how I
would
> set up my Query for this with regards to a CRecordSet.
>
> I set the log with a CTime::GetCurrentTime();, and wonder just how I
format
> the filter for this in extracting the queried data.
>
> I would need to do something with the m_strFilter such as "Date = ???",
but
> what is the required format? Should I be converting it using the
> CTime.Format() method, or do I require the conversion to a
TIMESTAMP_STRUCT?
>
> Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would
be
> to late in the query process to be of any use.
>
> The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
> 00:00:00}", however, this fails under my tests. Exactly how would I be
able
> to query this table for all data on X date?
>
> Any help would be greatly appreciated.
>
>
>
>
>
> PC'ing you,
> Dana M. Epp
> eppdm@uniserve.com
> http://bedrock.cyberhq.com/dana
>
> "How can one work with the technology of today, using yesterdays
> software and methods, and still be on the leading edge tomorrow?
> Why settle for less... I won't! "
>
-----From: Phil Reeves
At 15:28 28/01/97 -0800, you wrote:
>Environment: NT 4.0, Win95 VC++ 4.2b
>
>The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
>00:00:00}", however, this fails under my tests. Exactly how would I be able
>to query this table for all data on X date?
>
Try the timestamp format
{ts 'YYYY-MM-DD HH:MM:SS'}
Which seems to work for me
Philip Reeves
Knowledge Engineer
*************************************
Royal Brompton Hospital
Sydney Street
London
SW3 6NP
UK
Tel: +44 (0)171 351 8702
Fax: +44 (0)171 351 8743
e-mail: p.reeves@rbh.nthames.nhs.uk
web: http://www.rbh.nthames.nhs.uk
*************************************
-----From: Emmanuel Valentin
Hi,
For a software, I'm currently writing, I used the syntax
m_strFilter = "DATEFIELD = " + "to_date('"+ m_date + "','DD/MM/YYYY')";
It works perfectly with ODBC and Oracle. I guess, the easiest way to compare dates
in a SQL statement, is to use the build-in conversion function of your database
Access use CVDate() function which convert something to a valid date.
Manu
-----Original Message-----
From: Dana M. Epp [SMTP:eppdm@uniserve.com]
Sent: Tuesday, January 28, 1997 11:29 PM
To: mfc-l@netcom.com
Subject: Query Date/Time Field
Environment: NT 4.0, Win95 VC++ 4.2b
Within an Access database, I need to be able to Query a Log Table for all
entries where the Date is equal to today. Could someone explain how I would
set up my Query for this with regards to a CRecordSet.
I set the log with a CTime::GetCurrentTime();, and wonder just how I format
the filter for this in extracting the queried data.
I would need to do something with the m_strFilter such as "Date = ???", but
what is the required format? Should I be converting it using the
CTime.Format() method, or do I require the conversion to a TIMESTAMP_STRUCT?
Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would be
to late in the query process to be of any use.
The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
00:00:00}", however, this fails under my tests. Exactly how would I be able
to query this table for all data on X date?
Any help would be greatly appreciated.
PC'ing you,
Dana M. Epp
eppdm@uniserve.com
http://bedrock.cyberhq.com/dana
"How can one work with the technology of today, using yesterdays
software and methods, and still be on the leading edge tomorrow?
Why settle for less... I won't! "
-----From: Dave Silber
Dana M. Epp wrote:
>
> Environment: NT 4.0, Win95 VC++ 4.2b
>
> Within an Access database, I need to be able to Query a Log Table for all
> entries where the Date is equal to today. Could someone explain how I would
> set up my Query for this with regards to a CRecordSet.
>
> I set the log with a CTime::GetCurrentTime();, and wonder just how I format
> the filter for this in extracting the queried data.
>
> I would need to do something with the m_strFilter such as "Date = ???", but
> what is the required format? Should I be converting it using the
> CTime.Format() method, or do I require the conversion to a TIMESTAMP_STRUCT?
>
> Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would be
> to late in the query process to be of any use.
>
> The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
> 00:00:00}", however, this fails under my tests. Exactly how would I be able
> to query this table for all data on X date?
>
> Any help would be greatly appreciated.
>
> PC'ing you,
> Dana M. Epp
> eppdm@uniserve.com
> http://bedrock.cyberhq.com/dana
>
> "How can one work with the technology of today, using yesterdays
> software and methods, and still be on the leading edge tomorrow?
> Why settle for less... I won't! "
For what it's worth, here's an example select statement we're using to
select a set of records that are timestamped today.
ssSql2 << "Select * " <<
"from DMS.DHVFO A, DMS.DHVFOSCH B " <<
"where A.FO_NUMBER = B.FO_NUMBER " <<
"and B.RECORD_IND = 'A'" <<
"and B.RECEIVE_DATE = CURRENT DATE "<<
ends;
We're using a DB2 database. CURRENT DATE is apparently a SQL
keyword. RECEIVE_DATE is a DATE field in the database (represented
by a CTime object in the CRecordset-derived class).
We also do queries based on a specific date, but in those cases we
have to format a date/time string that DB2 expects.
Dave Silber
dns@hrb.com
Kevin Tarn -- kevin@pln.com.tw
Friday, January 31, 1997
Please try this,
void CMyRecordSet::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CMyRecordSet)
pFX->SetFieldType(CFieldExchange::outputColumn);
......
RFX_Date(pFX, _T("[qur_dat]"), m_qur_dat);
......
//}}AFX_FIELD_MAP
pFX->SetFieldType(CFieldExchange::param);
RFX_Date(pFX, _T("BeginDateParam"), m_BeginDateParam);
RFX_Date(pFX, _T("EndDateParam"), m_EndDateParam);
}
CTime curTime = CTime::GetCurrentTime();
m_mySetm_strFilter = _T("qur_dat >= ? AND qur_dat <= ?");
m_BeginDateParam = CTime(curTime.GetYear(), curTime.GetMonth(), curTime.GetDay(), 0, 0, 0);
m_EndDateParam = CTime(curTime.GetYear(), curTime.GetMonth(), curTime.GetDay(), 23, 59, 59);
Hope it helpful.
Kevin Tarn
POWER-LINE International Inc.
-----Original Message-----
From: Dana M. Epp [SMTP:eppdm@uniserve.com]
Sent: Wednesday, January 29, 1997 7:29 AM
To: mfc-l@netcom.com
Subject: Query Date/Time Field
Environment: NT 4.0, Win95 VC++ 4.2b
Within an Access database, I need to be able to Query a Log Table for all
entries where the Date is equal to today. Could someone explain how I would
set up my Query for this with regards to a CRecordSet.
I set the log with a CTime::GetCurrentTime();, and wonder just how I format
the filter for this in extracting the queried data.
I would need to do something with the m_strFilter such as "Date = ???", but
what is the required format? Should I be converting it using the
CTime.Format() method, or do I require the conversion to a TIMESTAMP_STRUCT?
Q114961 explains the use of of the TIMESTAMP_STRUCT... but its use would be
to late in the query process to be of any use.
The sample app odbcdate.exe from MS uses the format "Date >= {ts YY/MM/DD
00:00:00}", however, this fails under my tests. Exactly how would I be able
to query this table for all data on X date?
Any help would be greatly appreciated.
PC'ing you,
Dana M. Epp
eppdm@uniserve.com
http://bedrock.cyberhq.com/dana
"How can one work with the technology of today, using yesterdays
software and methods, and still be on the leading edge tomorrow?
Why settle for less... I won't! "
| Вернуться в корень Архива
|