|
Home > Archive > MS SQL Server > October 2006 > ASP query to SQL db
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ASP query to SQL db
|
|
| amatuer 2006-10-24, 6:38 pm |
| <% 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
| |
| Tibor Karaszi 2006-10-24, 6:38 pm |
| 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
>
| |
|
|
| Tibor Karaszi 2006-10-24, 6:38 pm |
| 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:
>
| |
| Norman Yuan 2006-10-24, 6:38 pm |
| 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
>
| |
| Tracy McKibben 2006-10-24, 6:38 pm |
| 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.
| |
| amatuer 2006-10-31, 12:15 am |
| thanx norman, i was blind that day..lol
Norman Yuan wrote:[color=darkred
]
> 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...
|
|
|
|
|