Solved

SQL for DATE Value

Posted on 2004-08-05
15
352 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
Comment Utility
1. Is your date format correct for the SQL server?

2.
0
 
LVL 3

Expert Comment

by:SuperUt
Comment Utility
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
Comment Utility
use BETWEEN like below

SELECT *
FROM SalesMaster
WHERE SALESDATE BETWEEN :MyStartSalesDate AND :MyEndSalesDate
0
 
LVL 4

Expert Comment

by:alikoank
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 12

Expert Comment

by:Ivanov_G
Comment Utility
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
Comment Utility
> 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
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Comment Utility
(3) SELECT *
FROM SalesMaster
WHERE DATE = 2004/08/04
0
 
LVL 3

Expert Comment

by:huferry
Comment Utility
0
 

Author Comment

by:joshfly
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now