?
Solved

SQL for DATE Value

Posted on 2004-08-05
15
Medium Priority
?
369 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 200 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

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.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

800 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