DateTime selection in SQL Server

Posted on 2003-03-23
Medium Priority
Last Modified: 2010-04-03
Hi, all

I have a datetime field in my datebase as the following. But I only want the date part in my date selection.

002700     2003-03-04 17:20:57.787
003725     2003-03-04 17:20:57.787
003726     2003-03-04 17:20:57.787
0037.7     2003-03-05 17:20:57.787
003731     2003-03-05 17:20:57.787
003732     2003-03-05 17:20:57.787
003489     2003-03-06 17:20:57.787
003490     2003-03-06 17:20:57.787
003491     2003-03-06 17:20:57.787
004251     2003-03-07 17:20:57.787
004252     2003-03-07 17:20:57.787
004253     2003-03-07 17:20:57.787
003851     2003-03-08 17:20:57.787
003852     2003-03-08 17:20:57.787
003853     2003-03-08 17:20:57.787
003962     2003-03-09 17:20:57.787
003963     2003-03-09 17:20:57.787
003964     2003-03-09 17:20:57.787

I want to return all of the above data from my database by inputing 2003-3-4 and 2003-3-9 in Delphi's DateTimePicker and then pass those two parameters in a SQL command as follows.

frmdata.tsqlInvoice.SQL.add('Select * from tablename');
frmdata.tsqlInvoice.SQL.add('where datacolumn between :begin and :end');
frmdata.tsqlInvoice.Params[0].AsDateTime  := dtpBegin.DateTime ;
frmdata.tsqlInvoice.Params[1].AsDateTime := dtpEnd.datetime;
frmData.tsqlInvoice.Prepare ;

Unfortunately the dataset returned is not as I desired. It excludes the data took place in 2003-3-4. How can I solve this problem?

Can you tell me what the problem is and how to get the desired query result in Delphi6.0 professional?

Great thanks

Question by:jasoncen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8192428
 I havn't tested this, but you might try changing the SQL line:

  frmdata.tsqlInvoice.SQL.add('where datacolumn between :begin and :end');


  frmdata.tsqlInvoice.SQL.add('where FLOOR(datacolumn) between :begin and :end');


LVL 27

Expert Comment

ID: 8193200

use trunc to get rid of the timefragment of the tdatetimepickers, like

frmdata.tsqlInvoice.Params[0].AsDateTime  := trunc(dtpBegin.DateTime) ;
frmdata.tsqlInvoice.Params[1].AsDateTime := trunc(dtpEnd.datetime);

meikl ;-)

Expert Comment

ID: 8193594
Dates and times are tricky.  The trouble is that "2003-3-4" without a times means 12:00:00AM on "2003-3-4" so truncating it will not work.  You can truncate and add 0.99999 (Most of a day.) that will put you somewhere at or around 11:59:59PM which is really what you wanted in the first place even if you didn't know it.

This will work most of the time.  The issue is that with certain dates and certain computers even if the data is the same, you'll get different results back.  The trick is to use the standardized SQL date formats in all your filters and where statements.  

Search the net for 'dd-mm-yyyy' and you'll see confirmation that this is the standard date format for sql statements.  I've also included below the standard date time format.
  SQL_DATE_FORMAT      = 'dd-mmm-yyyy'; // Standardized Format string for dates in SQL statements.
  SQL_DATETIME_FORMAT  = 'mmm-dd-yyyy HH:NN:SS';

The trouble is that if you don't do this and some user has set their regional date format to something non-default so that Word shows dates they way they want, Your SQL statment will return different results.  We found this out the hard way and yes with clients in the USA.  We were putting the numeric equivelant of the date into the SQL statement but it would not always work and NULLs would behave differently depending on whether or not the field had a default value.

So in short do this:
 DataSet.Filter := 'datacolumn < ''' +
        FormatDateTime(ShortDateFormat, dtpkDateThru.Date + 1) + '''';
or this:
   SQL.Text := SQL.Text +
   '(datacolumn BETWEEN "' + FormatDateTime(SQL_DATE_FORMAT, dtpkDateFrom.Date) + '" AND "'
                            + FormatDateTime(SQL_DATE_FORMAT, dtpkDateThru.Date) + '") ';

or this:
  frmdata.tsqlInvoice.Params[0].AsString  := FormatDateTime(SQL_DATE_FORMAT, dtpkDateFrom.Date);
  frmdata.tsqlInvoice.Params[1].AsString := FormatDateTime(SQL_DATE_FORMAT, dtpkDateThru.Date + 0.99999);
And if you can save space and time by removing the time from DataColumn.  If it is not needed.  It probably is...
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

TAZI earned 150 total points
ID: 8202015

This should do it ...

I want to return all of the above data from my database by inputing 2003-3-4 and 2003-3-9 in Delphi's DateTimePicker and then pass those two parameters in a SQL command as follows.

create 2 variables namely:
Date1, Date2 : String

Date1 := DateToStr(dtpBegin.Date) + '00:00:00 AM'
Date2 := DateToStr(dtpEnd.Date) + '23:00:00 PM'

Select * from tablename where datacolumn between :begin and :end

frmdata.tsqlInvoice.Params[0].AsString  := Date1;
frmdata.tsqlInvoice.Params[1].AsString := Date2;


Expert Comment

ID: 8240459
FormatDateTime('dd-mm-yyyy', DateTimeRecord) then convert this to TDate with strToDate().

Author Comment

ID: 8536985

Thank you very much for your help!


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question