Solved

SQL for DATE Value

Posted on 2004-08-05
15
363 Views
Last Modified: 2010-04-05
Hello, im encounter a SQL problem, i want to create a report's filter that allow user to select a Start Date and End Date to display all the sales between the date.

By looking at the SQL statement below is actually nothing but when i select the SAME date for both start date and end date, the report display nothing, just blank.

Quate of my SQL Statement
---------------------------------
SELECT *
FROM SalesMaster
WHERE SALESDATE >= :MyStartSalesDate AND SALESDATE <= :MyEndSalesDate

how can i solve this problem...

Thanks
josh
0
Comment
Question by:joshfly
  • 4
  • 3
  • 2
  • +4
15 Comments
 
LVL 3

Expert Comment

by:SuperUt
ID: 11724580
1. Is your date format correct for the SQL server?

2.
0
 
LVL 3

Expert Comment

by:SuperUt
ID: 11724628
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


0
 
LVL 4

Expert Comment

by:alikoank
ID: 11724658
use BETWEEN like below

SELECT *
FROM SalesMaster
WHERE SALESDATE BETWEEN :MyStartSalesDate AND :MyEndSalesDate
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 4

Expert Comment

by:alikoank
ID: 11724669
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.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11724709
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;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11724717
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;
0
 
LVL 3

Expert Comment

by:huferry
ID: 11725282
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
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11726182
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;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11727561
> 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;
0
 
LVL 4

Expert Comment

by:alikoank
ID: 11728122
> 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
0
 

Author Comment

by:joshfly
ID: 11732291
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

0
 
LVL 3

Accepted Solution

by:
jpedef earned 50 total points
ID: 11732992
SQL Server Books onlinse says:
"If only a date is specified, the time defaults to 12:00 A.M. (Midnight)."

So if your field stores also timepart then you should use time also. In Delphi 7 I use following functions in sql:

StartDate := FormatDatetime('YYYYMMDD HH:NN', StartOfTheDay(Date));
EndDate := FormatDatetime('YYYYMMDD HH:NN', EndOfTheDay(Date));

This YYYYMMDD dateformat is understood by every Sql server no matter what are  regional settings.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11733299
(3) SELECT *
FROM SalesMaster
WHERE DATE = 2004/08/04
0
 
LVL 3

Expert Comment

by:huferry
ID: 11736139
0
 

Author Comment

by:joshfly
ID: 11749436
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 ;)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

837 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