SQL for DATE Value

1. Is your date format correct for the SQL server?

2.

1. Is your date format correct for the SQL server?

This works on an Oracle database and solves any date format issue
    'TO_DATE('+ Enquote( FormatDateTime('dd-mm-yyyy', td))+',''DD-MM-YYYY'')';



2. Are you using dbExpress?

In that case you have supply your date parameter as AsSQLTimeStamp instead of AsDate

Info on http://community.borland.com/article/0,1410,30078,00.html

use BETWEEN like below

SELECT *
FROM SalesMaster
WHERE SALESDATE BETWEEN :MyStartSalesDate AND :MyEndSalesDate

if you supply the same value for MyStartSalesDate and MyEndSalesDate in

WHERE SALESDATE >= :MyStartSalesDate AND SALESDATE <= :MyEndSalesDate

this is the same as saying

WHERE SALESDATE = :MyStartSalesDate

which means SALESDATE must be equal to MyStartSalesDate down to milliseconds.

Its quite normal that this query returns an empty resultset.

procedure TForm1.SelectSalesByPeriod(MyStartSalesDate, MyEndSalesDate: TDateTime);
var
  S:      string;
begin
  S := ''
     + 'SELECT * FROM SalesMaster WHERE SALESDATE >='
     + ''''
     + FormatDateTime('yyyy/mm/dd', MyStartSalesDate)
     + ''''
     + ' AND SALESDATE <='
     + ''''
     + FormatDateTime('yyyy/mm/dd', MyEndSalesDate)
     + ''''
     + '';
  ADOQuery1.SQL.Text := S;
end;

procedure TForm1.SelectSalesByPeriod(MyStartSalesDate, MyEndSalesDate: TDateTime);
var
  S:      string;
begin
  S := ''
     + 'SELECT * FROM SalesMaster WHERE SALESDATE >='
     + ''''
     + FormatDateTime('yyyy/mm/dd', MyStartSalesDate)
     + ''''
     + ' AND SALESDATE <='
     + ''''
     + FormatDateTime('yyyy/mm/dd', MyEndSalesDate)
     + ''''
     + '';
  ADOQuery1.Active := False;
  ADOQuery1.SQL.Text := S;
  ADOQuery1.Active := True;
end;

If you use Access database then you can
use the TimeSerial and DateSerial.

SELECT *
FROM MyTable
WHERE (MyDate>=DateSerial(2000,1,1)+TimeSerial(0,0,0)) and
           (MyDate<=DateSerial(2001,12,31)+TimeSerial(23,59,59));

If you use MS SQL Server then you can
use the Convert function.


SELECT *
FROM MyTable
WHERE (MyDate>=CONVERT(datetime, '01/01/2000 00:00:00')) AND
           (MyDate<=CONVERT(datetime, '31/12/2001 23:59:59'));


I preffer using this conversion function to avoid different kinds of date format.

DateSerial is always DateSerial(yyyy,mm,dd) and Convert is always Convert(datetime, 'dd/mm/yyyy hh:mm:ss').

regards,
huferry

Don't use FormatDateTime routines. They depend on the regional settings. Instead you can convert the date as Float, because the DateTime is real type - the integer part contains the date, the fraction - time.

So put your query in some DataSet:
SELECT * FROM SalesMaster
WHERE SALESDATE >= :MyStartSalesDate
    AND SALESDATE <= :MyEndSalesDate

set Params property with 2 parameters and then:
ADOQuery1.Parameters.ParamByName('DATE_START').Value := DateTimePicker1.DateTime;
ADOQuery1.Parameters.ParamByName('DATE_END').Value := DateTimePicker2.DateTime;

> Don't use FormatDateTime routines. They depend on the regional settings.

Ivanov_G is right:

  FormatDateTime('yyyy/mm/dd', 38204.7370250926);
returns on my computer:
  2004.08.05
but I expected:
  2004/08/05

So please ignore my above posts....

//........

if you use is MS SQL Server database:

uses
  ...., DateUtils;

procedure TForm1.SelectSalesByPeriod(MyStartSalesDate, MyEndSalesDate: TDateTime);
var
  S:      string;
  T:      string;
  R:      string;
begin
  T := IntToStr(YearOf(MyStartSalesDate)) + '/'
     + IntToStr(MonthOf(MyStartSalesDate)) + '/'
     + IntToStr(DayOf(MyStartSalesDate));
  R := IntToStr(YearOf(MyEndSalesDate)) + '/'
     + IntToStr(MonthOf(MyEndSalesDate)) + '/'
     + IntToStr(DayOf(MyEndSalesDate));
  S := ''
     + 'SELECT * FROM SalesMaster WHERE SALESDATE >='
     + '''' + T + ''''
     + ' AND SALESDATE <='
     + '''' + R + ''''
     + '';
  ADOQuery1.Active := False;
  ADOQuery1.SQL.Text := S;
  ADOQuery1.Active := True;
end;

> Don't use FormatDateTime routines.
That is a bit harsh. Yes they do rely on Regional settings but you can make them work as you want using date format variables.

Eg:

DateSeparator:='/';
FormatDateTime('yyyy/mm/dd', 38204.7370250926);
returns  2004/08/05

DateSeparator:='.';
FormatDateTime('yyyy/mm/dd', 38204.7370250926);
returns  2004.08.05

im using MS SQL SERVER...

and also, if only specific a date it still can not display the record

SQL statement (Which i had tried)
-----------------
(1) SELECT *
FROM SalesMaster
WHERE DATE = GetDate()  // not work

(2) SELECT *
FROM SalesMaster
WHERE DATE = CONVERT(DATETIME,'04/08/2004)

(3) SELECT *
FROM SalesMaster
WHERE DATE = 04/08/2004


headache....T_T, why can not display the record, anyone know?

thanks for all who reply my post..
josh

(3) SELECT *
FROM SalesMaster
WHERE DATE = 2004/08/04

For more information on the CONVERT function

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Thanks jpedef, im using your example to solve my problem, but i made some modification.

the start and end date is select by the user from a date combo box, so, the HH:NN will always be 00:00, since te user only select the date not the time.

Then i change replace the both HH:NN to this :-
StartDate := FormatDatetime('YYYYMMDD 00:00, StartOfTheDay.Date);
EndDate := FormatDatetime('YYYYMMDD 23:00, EndOfTheDay.Date);

this code giving a range of the date....thanks alot

other experts also is GREAT!! i will award points to you all, once i post anoter questions. thanks alot

josh ;)