Determining the earlier of two dates

Results 1 to 4 of 4

Thread: Determining the earlier of two dates

  1. #1
    Join Date
    Dec 1969
    Posts
    3

    Default Determining the earlier of two dates

    I need a piece of SQL that selects all records from a table where the record&#039;s "PublishDate" is less than or equal to today&#039;s date. I am using SQL Server 2000. This is the sort of thing I&#039;m after:<BR><BR>SQLQuery = "SELECT ID,PublishDate,Title FROM TAB_ARTICLE WHERE PublishDate &#060;= " & date() & " ORDER BY PublishDate DESC"<BR><BR>Now, I know this won&#039;t work as the PublishDate value is treated as a string. I think I need something like Oracle&#039;s to_date() function but there doesn&#039;t seem to be an equivalent for SQL Server.<BR><BR>If anyone has a solution or knows of a work around I&#039;d be grateful of the support.<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969
    Posts
    497

    Default I think what you want is...

    If Pure SQL:<BR> Declare @Now datetime<BR> Set @Now = GetDate()<BR><BR> SELECT ID,PublishDate,Title FROM TAB_ARTICLE WHERE PublishDate &#060;= @Now ORDER BY PublishDate DESC<BR><BR>If Asp (Before Current Time; e.g. this morning or before):<BR> SQLQuery = "SELECT ID,PublishDate,Title FROM TAB_ARTICLE WHERE PublishDate &#060;= Convert(datetime,&#039;" & date() & "&#039;) ORDER BY PublishDate DESC"<BR><BR>if Before TODAY:<BR> SQLQuery = "SELECT ID,PublishDate,Title FROM TAB_ARTICLE WHERE PublishDate &#060;= Convert(datetime,Convert(nvarchar,&#039;" & date() & "&#039;,101)) ORDER BY PublishDate DESC"<BR><BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    3

    Default Aren’t you trying to trying to convert the wrong p

    BTW it&#039;s ASP.<BR><BR>Surely the date() value is already going to be the correct datatype so I don&#039;t see the point in trying to convert it - could be wrong though. Similarly, the PublishDate record value is stored in the database as datetime value so that shouldn&#039;t be a problem either. So, I&#039;m stumped. If I don&#039;t try to convert anything (ie ... WHERE PublishDate &#060;= " & date() & " ORDER BY ...) I get no records (EOF or BOF) which isn&#039;t right as all the records have dates less than today&#039;s dates.<BR><BR>Still, I tried your code and got the following errors:<BR><BR>Your second example:<BR>[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. <BR><BR>Your third example:<BR>[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.<BR><BR>Needless to day I didn&#039;t try your first example as I&#039;m working with ASP.<BR><BR>I&#039;m fresh out of ideas, the only other thing I can do if filter out the unwanted records after the SQL has been executed but I didn&#039;t really want to do that.

  4. #4
    Join Date
    Dec 1969
    Posts
    3

    Default Solution

    I ended up using the Between statement (with the first date being one before any of my records) and it worked. Thanks all the same.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •