still need date help, argghhh 1

[taval]

I have a database in which I want to execute a query that takes an integer. The query finds the user who has not back up the system for xx (integer) amount of days.<br><br>The database has a entity called LastModified that when displayed on the screen takes the format such as:<br><br>4/4/00 11:07:44 PM<br>the date part is in the mm/dd/yy format.<br><br>the table that holds the entity LastModified is called FileDescription and the value I want to find is the CustID ,that is also an entity of the FileDescription table.<br><br>I want to know how to write the query so that it takes the integer from the text box measures it against the current date to find who has backed up in that time period. So for example if I entered 30 into the text box, the SQL query would work out how many customers (CustID) backed up in the last 30 days.<br><br>I'm using MS SQL server, but I'm really having difficulty with using dates/times. Heres a breakdown of my problem.<br><br>I have a date field used in my database, when I see this date on the SQL server after I have run a query the date is shown as e.g. : 2000-02-18 19:14:59.660<br><br>When a run the query on an asp page, the date is shown as e.g.: 1/26/98 3:45:32 PM<br><br>Heres a snippet of the code that gives me problems :<br><br>------------------------------------<br><br>dLastMod = DateAdd(&quot;d&quot;, -5, Now())<br>Response.Write &quot;Current Date: &quot; & Now() & &quot;&lt;br&gt;&quot;<br>Response.Write &quot;Find Date:&quot; & dLastMod & &quot;&lt;br&gt;&quot;<br>Response.Write FormatDateTime(dLastMod,2) & &quot;&lt;br&gt;&quot;<br>datelast = FormatDateTime(dLastMod,2)<br>datelast = datelast & &quot;12:00:00 AM&quot;<br>Response.Write datelast & &quot;&lt;br&gt;&quot;<br>Response.Write &quot;Backup Works&quot; & request.form(&quot;days&quot & &quot;&lt;br&gt;&quot;<br><br>StrQuery = &quot;Select CustId from FileDescription where FileDescription.UploadDate &gt; &quot; & dLastMod<br><br>----------------------------------------<br><br>The problem I suspect is with the query itself.<br><br>Heres the error message I get:<br><br>--------------------------------------<br>Microsoft OLE BD Provider for ODBC Drivers error '80040e14'<br><br>Incorrect syntax near '11' &lt;-(the time part)<br>line 35, &lt;-(StrQuery line)<br>---------------------------------------------<br><br>I have tried using the date part (datelast in code above) by itself but this just shows all the records in the database.<br><br>If you need more info plz tell me.<br>I posted this message before but got no reply, it has serveral replys that did'nt help. The topic was called<br>&quot;SQL query involving a date, plz help!!&quot;<br>I would be extremly grateful for any help.<br><br>Taha

 

[palbano]

Dear taval,<br><br>You should be able to perfom this query correctly in the SQL Server environment before trying to implement it in an ASP page code block. Once you know the exact syntax required by SQL Server to achieve your desired result, getting the correct SQL Statement into a string in your ASP code is a simple matter. <br><br>Until you know the exact syntax for SQL Server you are just guessing.<br><br>Good luck<br>-pete

 

[taval]

Pete, the thing is I can't get it to work correctly on SQL server. If anyone uses a database that uses dates and runs it on SQL server could they plz tell me how they run queries involving the date?<br><br>Thankx.

 

[palbano]

Dear taval,<br><br>&gt; Pete, the thing is I can't get it to work correctly on SQL server. <br><br>Then why would you try to get it to run from an ASP page? &lt;confused&gt; (<br><br>&gt; If anyone uses a database that uses dates and runs it on SQL server could they plz tell me how they run queries involving the date?<br><br>You should ask that in the 'SQL' or 'SQL Server' forums. The guys in there know there stuff!!!<br><br>Good luck<br>-pete<br>

 

[jfriestman]

From your problem description, it doesn't sound like you need to construct a date with ASP, just use the DateDiff function in SQL Server.<br><br>I wrote a Stored Procedure that will do it for you.&nbsp;&nbsp;You will have to modify it slightly to work with your DB, I wrote this and tested it with Northwind so you could see it working.<br><br><br>To create the procedure, just copy/paste it into QueryAnalyzer and run the command.<br><br>USE Northwind<br>IF OBJECT_ID ('dbo.UP_CheckDays') IS NOT NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DROP PROCEDURE dbo.UP_CheckDays<br>GO<br>CREATE PROCEDURE dbo.UP_CheckDays<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@NumDays INT<br>AS<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT * FROM Northwind.dbrders<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE DateDiff(day, OrderDate, GetDate()) &gt; @NumDays<br>GO<br><br>You can then test it in Query Analyzer like this:<br>EXEC UP_CheckDays 365<br><br>You can call this procedure in your ASP page like so...<br>Rs.Open &quot;EXEC UP_CheckDays &quot; & Request.Form(&quot;NumDays&quot<br><br>Or you can just convert the procedure into a simple SQL statement and put it somewhere in your page.<br><br>Happy programming. <p> Jeff Friestman<br><a href=mailto: > </a><br><a href=

http://www.brainbench.com/transcript.jsp?pid=61192>

View my Brainbench transcript</a><br>Brainbench 'Most Valuable Professional' for ASP<br>

http://www.brainbench.com

 

[taval]

Thanks alot people, and thanks jeff, my query now works.

 

[hobo]

taval,<br><br>if you are trying to compare a date to a date field in sql server you should be able to input any valid vb date format and get a result.<br><br>you to change you query so the date is inside single quotes<br><br>eg.<br>select name, date from tblUser where date=' & indate & '<br><br>don't know how to post with double quotes in place so put them in whre necessary<br>