Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Identify record in period

    I have a transaction table and I have a account table.

    In the account table I have a StartDate and EndDate both are DataTypes nVarChar(50)

    In the transaction table I have bookings with a EnterDate (nVarChar(50))

    I need to identify which records in the Trans table fall within the Start and End Date of the account table.

    This I need to show in a SQL View.


    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can't be done with nvarchar dates

    change your tables and convert your dates, and then it will be possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Two questions now:
    1. How to I convert my dates (I have no problem changing the field type as this is a new table)?
    2When I have it in date format what would the SQL be to identify those records?

    Many many thanks,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i can't tell you how to convert the dates, beause i can't see what format they're in now

    if you can rebuild the tables and reload the data, that would be best

    as for the query, just do an INNER JOIN using BETWEEN in the ON clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    If I do that will it not only show the records that fall between these dates. What I want to do is show all records and identify the one that fall within these dates by creating another filed in the view.

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I can get this to work if I link MS Access to the tables and write the query there but if I transfer this to SQL Server the SQL does not work.
    This is the SQL in MS Access

    SELECT IIf(Transactions.ConnectTime<Conf_Dates.StartDate, 'Yes','No') AS StartOK
    FROM Transactions LEFT JOIN Conf_Dates ON CDR_Transactions.ConfRefNum = Conf_Dates.ConfRefNum;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the IIF function and, instead, use a CASE expression

    the syntax is in da manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Got it and now working good - Thats what I was looking for.

    Many thanks

Posting Permissions

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