Dates in ASP and SQL Server 2000

Results 1 to 3 of 3

Thread: Dates in ASP and SQL Server 2000

  1. #1
    Join Date
    Dec 1969

    Default Dates in ASP and SQL Server 2000

    I am having a problem comparing dates in ASP to SQL Server 2000. I did have this working now for some reason it is not. Dates are stored in SQL Server in datetime datatype. I want to convert strings to dates in the format dd / mm /yyyy (including slashes). On a seperate page - new records are created and using cdate (string) it appears to submit data fine.<BR><BR>I originally used CONVERT(DATETIME,&#039;05/09/1998&#039;, 102) as part of an sql statement to compare a submitted date to one in the database:-<BR><BR>strSQL = "SELECT * FROM contract WHERE ContractNumber LIKE &#039;" & ContNumold & "&#039; AND StartofContract LIKE CONVERT(DATETIME,&#039;05/09/1998&#039;, 102)" <BR><BR>But it no longer works. Without the date part it selects data fine. However this is part of a primary key so is essential. <BR><BR>I am nto sure why this code no longer works as I had it working briefly. But no records are returned even though there is a record that matches what I submit. Is there a better way of doing this? Am I doing right?<BR><BR>Regards

  2. #2
    Join Date
    Dec 1969

    Default You can't use LIKE with datetime!

    LIKE *only* works with the various kinds of text fields.<BR><BR>And it&#039;s silly to use LIKE for your ContractNumber, as well, since you aren&#039;t using any wildcards. There is NO POINT in using LIKE with *anything* except a *string* that contains wildcard characters (e.g., WHERE city LIKE &#039;%ville%&#039;).<BR><BR>I *think* that if you simply change your two uses of LIKE in that query to =, then all will work.<BR><BR>Why do you use a text field for something that you label "ContractNumber"??? If it isn&#039;t a number, why do you call it that?<BR><BR>You know, you really do *NOT* need to use CONVERT with dates in standard formats. While I believe this will work:<BR><BR>strSQL = "SELECT * FROM contract WHERE ContractNumber = &#039;" & ContNumold & "&#039; " _<BR> & " AND StartofContract = CONVERT(DATETIME,&#039;05/09/1998&#039;, 102)" <BR><BR>It is also true that if your SQL Server machine is located in the USA, and if indeed StartofContract is a DATETIME field, then this works equally well:<BR><BR>strSQL = "SELECT * FROM contract WHERE ContractNumber = &#039;" & ContNumold & "&#039; " _<BR> & " AND StartofContract = &#039;05/09/1998&#039; " <BR><BR>Because SQL Server automatically converts strings to datetime values when assigning to or comparing agains datetime fields.<BR><BR>If you aren&#039;t located in the USA and want to use the MM/DD/YYYY format, then indeed the CONVERT is a good idea. <BR><BR>You might want to read this FAQ article I wrote a while back:<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Not strictly true

    &#060;Quote BOL ---&#062;<BR>Pattern Matching with LIKE<BR>It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 1998-12-31 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. However, the clause WHERE arrival_time LIKE &#039;%9:20%&#039; does find it.<BR>&#060;end Quote&#062;

Posting Permissions

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