sum price between 2 dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum price between 2 dates

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 10:32:14

I have a column Price (FLOAT)
and column BuyingDate (DateTime)
in a table Orders

I have 2 textbox containing dates picked from a calendar
dim date1 as datetime = ctype(textbox1.text,datetime)
dim date2 as datetime = ctype(textbox2.text,datetime)

i try to:
SELECT SUM(Price) FROM Orders WHERE BuyingDate BETWEEN " & date1 & " AND " & date2

the query returning null

what is wrong?

I tried not to convert the textbox.text to datetime
i tried to write in the textbox the month before the day and the opposite
i tried >= and <= instead of BETWEEN
and its not working
Please HELP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:53:35

and are you sure that you've data existing in table for date range/ ALso does date1 and date2 contain only date or time part as well?

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 11:42:19

quote:

and are you sure that you've data existing in table for date range

yes

quote:
does date1 and date2 contain only date or time part as well?

i get the date from a calendar

tbDate1.Text = Calendar1.SelectedDate.ToString()

i tried also without .tostring

i triea also to write directly into the textbox 1-1-2008 and 1/1/2008 and 20080101 and 1-jan-2008

and a lot other options

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 11:46:31

can you try running the query by hardcoding dates and see if it returns a value?

SELECT SUM(Price) FROM Orders WHERE BuyingDate BETWEEN '20080101' AND yourenddatevaluehere

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 11:52:36

Its working!

does the date must be enclosed like that '20080101'??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 11:58:20

yup. it should be. try the below then

SELECT SUM(Price) FROM Orders WHERE BuyingDate BETWEEN '" & date1 & "' AND '" & date2 & "'"

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 12:04:05

when i get the date from the calendar i get an error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

but if i write into the textbox '1-1-2008' or '1/1/2008'
its working fine

i assume i have to get rid of the time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 12:05:47

whats the format you get date while selecting from calendar?

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-02 : 12:09:21

12/10/2008 00:00:00

dd/mm....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 12:12:01

ok then try this
"SET DATEFORMAT dmy;SELECT SUM(Price) FROM Orders WHERE BuyingDate BETWEEN '" & date1 & "' AND '" & date2 & "'"