Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

DateTime selection in SQL Server

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

1 Solution
 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');



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 ;-)
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...
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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;

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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now