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.
| Author |
Topic |
|
missinglct
Yak Posting Veteran
75 Posts |
 Posted - 2004-05-11 : 16:29:54
|
| Hello everyone,I am trying to write an SQL that displays the results for the current year (2004). So, I started writting some variables like this:StartDate = "1/1" EndDate = "12/31"StartYear = Request("listname")EndYear= Request("listname") StartDateMonthYear = StartDate & "/" & StartYearEndDateMonthYear = EndDate & "/" & EndYear And here is my SQL string:SQL="SELECT que.que, ans.ansdesc, SurveyOrder.OrderNum, que.queID, OrgAnswers.totalhits FROM (((((que INNER JOIN SurveyOrder ON que.Queid = SurveyOrder.QueID ) INNER JOIN Category ON que.CategoryID = Category.CategoryID) INNER JOIN ans ON que.queID = ans.queID) INNER JOIN OrgAnswers ON ans.AnsID = OrgAnswers.AnsID) INNER JOIN CommandHierarchy ON OrgAnswers.CommandHierarchyNodeID = CommandHierarchy.CommandHierarchyNodeID) INNER JOIN SurveyTakenByCommands ON CommandHierarchy.CommandHierarchyNodeID = SurveyTakenByCommands.CommandHierarchyNodeID WHERE ans.CategoryID=1 AND OrgAnswers.CurrentDate Between '" StartDateMonthYear & "' AND '" & EndDateMonthYear & "'" ORDER BY SurveyOrder.OrderNum ASC" I did a response.write to see what it looks like before I print it out so I write this line:Response.Write SQLAnd this is what I see: SELECT que.que, ans.ansdesc, SurveyOrder.OrderNum, que.queID, OrgAnswers.totalhits FROM (((((que INNER JOIN SurveyOrder ON que.Queid = SurveyOrder.QueID ) INNER JOIN Category ON que.CategoryID = Category.CategoryID) INNER JOIN ans ON que.queID = ans.queID) INNER JOIN OrgAnswers ON ans.AnsID = OrgAnswers.AnsID) INNER JOIN CommandHierarchy ON OrgAnswers.CommandHierarchyNodeID = CommandHierarchy.CommandHierarchyNodeID) INNER JOIN SurveyTakenByCommands ON CommandHierarchy.CommandHierarchyNodeID = SurveyTakenByCommands.CommandHierarchyNodeID WHERE ans.CategoryID=1 AND OrgAnswers.CurrentDate Between '1/1/2004, ' AND '12/31/2004, ' ORDER BY SurveyOrder.OrderNum ASC My question is: Why did it display a comma (,) at the end of the year???????? the correct one should look like this:...... OrgAnswers.CurrentDate Between '1/1/2004' AND '12/31/2004'This is a mix between SQL and ASP and since this is a Forum for SQL, I am not sure how helpful it is to post it here but I hope there is someone from here who has both knowledges on ASP and SQL :) Thank you in advance. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-11 : 16:36:43
|
| Did you do a Response.Write on StartDateMonthYear and EndDateMonthYear before it was put into your SQL statement?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-11 : 17:33:28
|
| You could always do a Replace(StartDateMonthYear , ",", "") to get around the problem.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
danielhai
Yak Posting Veteran
50 Posts |
Posted - 2004-05-12 : 19:17:03
|
| having two form elements on a page with the same name will result in the value for that element to be seperated with a comma. You should split out your results for beginning and ending dates as different field names. |
|
|
|
|
|
|
|
|
A comma appears after the date!!