1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development How do I write a SQL query involving a time stamp?

Discussion in 'Software' started by Captain Xarzu, 26 Apr 2016.

  1. Captain Xarzu

    Captain Xarzu What's a Dremel?

    Joined:
    22 Aug 2007
    Posts:
    15
    Likes Received:
    0
    How do I write a SQL query involving a time stamp?

    QUOTE_ID is a character string
    CREATE_DTG is a date time type
    RETENTION_LEAD_TRACK is a table

    This sql statement cmf.CommandText = "SELECT QUOTE_ID FROM RETENTION_LEAD_TRACK where CREATE_DTG > '2016 - 04 - 25 18:18:15.2891'"

    throws this error {"Conversion failed when converting date and/or time from character string."}
     
  2. deathtaker27

    deathtaker27 Modder

    Joined:
    17 Apr 2010
    Posts:
    2,221
    Likes Received:
    180
    What sql database are you writing this for?
     
  3. faugusztin

    faugusztin I *am* the guy with two left hands

    Joined:
    11 Aug 2008
    Posts:
    6,953
    Likes Received:
    270
  4. bigc90210

    bigc90210 Teh C

    Joined:
    7 Oct 2003
    Posts:
    1,244
    Likes Received:
    65
    spaces between the YYYY-MM-DD may be your issue?
     
  5. phinix

    phinix RIP Waynio...

    Joined:
    28 Apr 2006
    Posts:
    6,000
    Likes Received:
    97
    I would use CONVERT to change it in a query
     
  6. cjmUK

    cjmUK Old git.

    Joined:
    9 Feb 2004
    Posts:
    2,553
    Likes Received:
    88
    I think it all depends on where your actual date is coming from...

    But if push comes to shove, use CAST over CONVERT because it is ANSI compliant and preserves precision:

    Code:
    ...WHERE CREATE_DTG > CAST('2016 - 04 - 25 18:18:15.2891' AS DateTime)
     

Share This Page