ASP query to SQL db

<% Datum = "1/1/2005"
Datum = cdate(datum)
Datum2 = datum + 9
Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
& ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
I am tryng to query some data according to dates...No matter hw I
change the format of the date variables, my recordset still returns no
data.
Any ideas, suggestions please. Any help will be gr8ly appreciated..thanxThis gives you a good overview of how you should pass datetime values and also recommendations on
how to construct searches on datetime ranges:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amatuer" <njoosub@.gmail.com> wrote in message
news:1161165428.268825.43060@.i3g2000cwc.googlegroups.com...
> <% Datum = "1/1/2005"
> Datum = cdate(datum)
> Datum2 = datum + 9
>
> Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
> Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
> sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
> SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
> AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
> & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
> I am tryng to query some data according to dates...No matter hw I
> change the format of the date variables, my recordset still returns no
> data.
> Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
>|||Thanx for the site Tibor. Contains alot of usefull and interesting
information, unfortunately none of them helpd solve my problem
Tibor Karaszi wrote:
> This gives you a good overview of how you should pass datetime values and also recommendations on
> how to construct searches on datetime ranges:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1161165428.268825.43060@.i3g2000cwc.googlegroups.com...
> > <% Datum = "1/1/2005"
> > Datum = cdate(datum)
> > Datum2 = datum + 9
> >
> >
> > Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
> > Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
> >
> > sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> > R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
> > SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
> > AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
> > & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> > R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
> >
> > I am tryng to query some data according to dates...No matter hw I
> > change the format of the date variables, my recordset still returns no
> > data.
> >
> > Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
> >|||Run a Profiler trace so you see that SQL command submitted to SQL Server. Format the datetime
strings so they are language neutral.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amatuer" <njoosub@.gmail.com> wrote in message
news:1161168200.659185.119520@.f16g2000cwb.googlegroups.com...
> Thanx for the site Tibor. Contains alot of usefull and interesting
> information, unfortunately none of them helpd solve my problem
> Tibor Karaszi wrote:
>> This gives you a good overview of how you should pass datetime values and also recommendations on
>> how to construct searches on datetime ranges:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amatuer" <njoosub@.gmail.com> wrote in message
>> news:1161165428.268825.43060@.i3g2000cwc.googlegroups.com...
>> > <% Datum = "1/1/2005"
>> > Datum = cdate(datum)
>> > Datum2 = datum + 9
>> >
>> >
>> > Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
>> > Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
>> >
>> > sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
>> > R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
>> > SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
>> > AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
>> > & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
>> > R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
>> >
>> > I am tryng to query some data according to dates...No matter hw I
>> > change the format of the date variables, my recordset still returns no
>> > data.
>> >
>> > Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
>> >
>|||You need to add single quote mark around the date value in WHERE clause;
also, since Datun and Datum2 have been already formatted into a "mm-dd-yyyy"
format (hopefully, your SQL Server recognise date that way, not
"dd-mm-yyyy"), you do not need to convert it back to Date type with cdate():
"SEELCT ... WHERE...(R_Reenval.Datum Between '" & Datum & "' And '" & Datum2
& "')
"amatuer" <njoosub@.gmail.com> wrote in message
news:1161165428.268825.43060@.i3g2000cwc.googlegroups.com...
> <% Datum = "1/1/2005"
> Datum = cdate(datum)
> Datum2 = datum + 9
>
> Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
> Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
> sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
> SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
> AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
> & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
> I am tryng to query some data according to dates...No matter hw I
> change the format of the date variables, my recordset still returns no
> data.
> Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
>|||amatuer wrote:
> <% Datum = "1/1/2005"
> Datum = cdate(datum)
> Datum2 = datum + 9
>
> Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
> Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
> sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
> SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
> AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
> & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
> I am tryng to query some data according to dates...No matter hw I
> change the format of the date variables, my recordset still returns no
> data.
> Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
Display the contents of the sql string somewhere on your page so that
you can see the SELECT statement that you've just built. I think
you'll see that you're missing single quotes around your date values.|||thanx norman, i was blind that day..lol
Norman Yuan wrote:
> You need to add single quote mark around the date value in WHERE clause;
> also, since Datun and Datum2 have been already formatted into a "mm-dd-yyyy"
> format (hopefully, your SQL Server recognise date that way, not
> "dd-mm-yyyy"), you do not need to convert it back to Date type with cdate():
> "SEELCT ... WHERE...(R_Reenval.Datum Between '" & Datum & "' And '" & Datum2
> & "')
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1161165428.268825.43060@.i3g2000cwc.googlegroups.com...
> > <% Datum = "1/1/2005"
> > Datum = cdate(datum)
> > Datum2 = datum + 9
> >
> >
> > Datum = Month(Datum) & "-" & Day(Datum) & "-" & Year(Datum)
> > Datum2 = Month(Datum2) & "-" & Day(Datum2) & "-" & Year(Datum2)
> >
> > sql = "SELECT R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> > R_Reenval.Dag, R_Reenval.Reenval_Silo, Sum(Reenval_Silo) AS
> > SumOfReenval_Silo FROM R_Reenval WHERE ((R_Reenval.NAAM)='klerksdorp')
> > AND (R_Reenval.Datum Between " & cdate(Datum) & " And " & cdate(Datum2)
> > & ") Group By R_Reenval.NAAM, R_Reenval.Jaar, R_Reenval.Maand,
> > R_Reenval.Dag, R_Reenval.Reenval_Silo ORDER BY Jaar, Maand, Dag" %>
> >
> > I am tryng to query some data according to dates...No matter hw I
> > change the format of the date variables, my recordset still returns no
> > data.
> >
> > Any ideas, suggestions please. Any help will be gr8ly appreciated..thanx
> >sql