SQL query involving a date, plz help!!

[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 hope I was clear, this problems been bugging me for a while so I would be extremely grateful for any help.<br><br><br>

 

[froggerIII]

Outside the query, do a calculation to find the date (call it dLastMod) x days ago using 'dLastMod = DateAdd(&quot;d&quot;, -x, Now())'. Then query the database for all values of LastModified which are after dLastMod. <p>ray<br><a href=mailto:rheindl@bju.edu>rheindl@bju.edu</a><br><a href= > </a><br>

 

[taval]

I have put this in my code:<br><br>dLastMod = DateAdd(&quot;d&quot;, -100, Now())<br>StrQuery = &quot;Select CustId from FileDescription where FileDescription.LastModified &gt; &quot; & dLastMod<br><br><br>is this correct?<br><br>thanks

 

[taval]

the problem I get is that I don't think I'm getting the desired results since I have a file modified on the 12/2/1999 but when I run the query in asp and on the SQL server itself and I get no results. The query I run is<br><br>StrQuery = &quot;Select CustId from FileDescription where FileDescription.LastModified = 12/2/1999&quot;<br><br>and I also have older modified files such as 12/1/1999, but when I run the query which is<br><br>StrQuery = &quot;Select CustId from FileDescription where FileDescription.LastModified &lt; 12/5/2000&quot;<br>(I assume that this shold find all files modified before 12/5/2000)<br><br>the files such as 12/1/1999 do not show up.<br><br>Can someone explain the problems I'm having?<br><br>&nbsp;<br><br>

 

[froggerIII]

Date and Time values are very tricky, you have to make sure they are in the right format for the database that you are using. For instance, ibm's db2 uses 'yyyy-mm-dd-hh.mm.ss.nnnnn', but access uses 'mm/dd/yyyy hh:mm:ss AM/PM'<br><br>before you do comparisons, make sure that in your query, the date you are comparing with is in the right format. one thing you might want to try if you are using access is after you call the dateadd function,<br><br>dLastMod = dLastMod & &quot;12:00:00 AM&quot; <p>ray<br><a href=mailto:rheindl@bju.edu>rheindl@bju.edu</a><br><a href= > </a><br>

 

[taval]

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 would be extremly grateful for any help.<br><br>Taha

 

[froggerIII]

You need to format your date and time values to match those of SQL Server.&nbsp;&nbsp;Something like this: <br><br>Function DateTimeToDBFormat(ByVal dDate)<br>&nbsp;&nbsp;&nbsp;If Minute(dDate) &lt; 10 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sMin = &quot;0&quot; & Minute(dDate)<br>&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sMin = Minute(dDate)<br>&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;DateTimeToDBFormat = Year(dDate) & &quot;-&quot; & Month(dDate) & &quot;-&quot; & Day(dDate) & &quot; &quot; & Hour(dDate) & &quot;:&quot; & sMin & &quot;:00.00&quot;<br><br>End Function<br><br>dLastMod = DateTimeToDBFormat(DateAdd(&quot;d&quot;, -5, Now))<br>StrQuery = &quot;Select CustId from FileDescription where FileDescription.UploadDate &gt; &quot; & dLastMod<br><br><br>Your database might require some of the values to have 2 digits, like minutes, days, months, etc.&nbsp;&nbsp;To fix this, just do what i have done to minutes in the above function. <p>ray<br><a href=mailto:rheindl@bju.edu>rheindl@bju.edu</a><br><a href= > </a><br>

 

[taval]

ray, I've tried doing what you said but it still shows the same error. I really I'm fed up with these date and time formats. I can't figure out why there is incorrect syntax when I do the (1/26/98 3:45:32 PM) type format, and when I just do the date it shows me all the fields. If anyone can help me out I would be ever so happy.<br><br>

 

[taval]

I've tried everything above but I still dont get the right result. Even on the SQL server when I run queries like:<br><br>Select CustId from FileDescription where FileDescription.UploadDate &gt; 1/26/98 12:00:00 PM<br><br>OR<br><br>Select CustId from FileDescription where FileDescription.UploadDate &gt; 2000-02-18 19:14:59.660<br><br>I still don't get the results , only an error message even when there are fields that satisfy the query.<br>