Исходники.RU - Информационный сервер для программистов: исходники со всего света.

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! "

| Вернуться в корень Архива |