Solved

SQL for DATE Value

Posted on 2004-08-05
15
365 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
[X]
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
  • 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
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!

 
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

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

Suggested Solutions

Title # Comments Views Activity
how to send memory stream from ics Client To ics server ? 11 204
update joined tables 2 72
scroll down TListBox component in Delphi 1 42
migrate this code to work on android 1 45
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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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