Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: 4/7/2003 vs 04/07/2003

    Hi,
    I have a parameter query in SQL. The Query selects records in a date range based on the parameters.

    my problem is that if I put the dates in the format of 4/7/2003 it won't work and I have to insert the date like 04/07/2003.

    I don't know how to fix this. Its important since the parameters are coming froman Access ADP front end through the calendar object which saves the date in the 4/7/2003 format.


    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    please post an example or your query!
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your time Paul.

    Here is my query:
    ----------------------------------------------------------------------------
    SELECT
    dbo.tblComIssue.ComID AS ID,
    CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '') + ' '
    + ISNULL(dbo.tblContact.LastName, '') AS Caller

    FROM dbo.tblComIssue LEFT OUTER JOIN
    dbo.tblContact ON
    dbo.tblComIssue.ContactID = dbo.tblContact.ContactID

    WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) BETWEEN @Date1 AND @Date2)
    ORDER BY dbo.tblComIssue.ComDate DESC


    -----------------------------------
    I also tried this instead of the "BETWEEN" function:

    WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) >= @Date1) AND (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) <= @Date2)
    ORDER BY dbo.tblComIssue.ComDate DESC
    ------------------------------------

    The wired part is that I have another query for "Date" not Date Range and it works fine:

    SELECT dbo.tblComIssue.ComID, CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '')
    + ' ' + ISNULL(dbo.tblContact.LastName, '') AS Caller
    FROM dbo.tblComIssue LEFT OUTER JOIN
    dbo.tblContact ON dbo.tblComIssue.ContactID = dbo.tblContact.ContactID
    WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) = @Date)
    ORDER BY dbo.tblComIssue.ComDate DESC

    ------------------

    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    It looks like your problem is comparing strings not dates. I would convert @Date1 & @Date2 to datetime datatype and compare dates.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks,
    Yes that was the problem.
    I used Enterprise manages to define the data types.

    Thanks paul

Posting Permissions

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