Results 1 to 6 of 6

Thread: Date

  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Post Unanswered: Date

    I need to display records with dates > than or = to system date. I tried the statement below with no luck.........how far off am I


    SELECT *
    FROM dbo.notice
    WHERE (publish=2) AND ClosingDate > = 'Date()'
    ORDER BY PostingNumber

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    48
    function GetDate() will return current system date and time,
    thus the query could be:

    SELECT *
    FROM dbo.notice
    WHERE (publish=2) AND ClosingDate >= GetDate()
    ORDER BY PostingNumber

    if you want to trim the time portion from return value of GetDate(),
    you can replace GetDate() above by
    Convert(datetime, Convert(varchar, GetDate(), 101), 101)
    Shianmiin

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    thanks, I'll give it a try

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "if you want to trim the time portion from return value of GetDate(),
    you can replace GetDate() above by
    Convert(datetime, Convert(varchar, GetDate(), 101), 101)"

    shianmiin, it is not necessary to reconvert your value to datetime. SQL Server implicitly translates valid date strings to datetime values.

    Convert(char(10), GetDate(), 120) is sufficient.

    blindman

  5. #5
    Join Date
    Nov 2003
    Posts
    48
    You are right about implicit conversion from char to date and that will save several characters of code.

    Since the conversion has to be done anyway, no matter implicitly or explicitly, I prefer to convert it to datetime explicitly so that we don't rely on what date format can be recognized by SQL Server for implicit conversion from char to datetime.

    I would call it preventive coding convention.

    ^^
    Shianmiin

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thats why I always convert to format 120. It sorts correctly as text and is recognized by all implementations of SQL Server.

    blindman

Posting Permissions

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