SUBSTRING a DATETIME value in a SELECT statement

  • Hi all,

    I am having issues when trying to truncate a DATETIME column using the SUBSTRING() function in a SELECT statement. I have tried using CAST and CONVERT functions on the DATETIME column but still get error messages. Is there another way of doing this?

    SELECT SUBSTRING(CAST(date AS NVARCHAR(11), 1, 10)) FROM table

    I am stumped with this

    Hope you can help.

    Tryst

  • What information are you trying to get from the DATETIME column?

  • For starters, get your close paren from the end of the expression to the end of the cast function...

    SELECT SUBSTRING(CAST(date AS NVARCHAR(11)), 1, 10)

    ..and then explain (as pam asked) the requirements...







    **ASCII stupid question, get a stupid ANSI !!!**

  • Trys this instead...

    SELECT CONVERT(VARCHAR(10),date,101)

    ... because the supbstring method on date castings will likely not five you the mm/dd/yyyy format that I think you're looking for.  And here's some other formating style numbers you can sub for the 101...

    yyyymmdd = 112

    dd/mm/yyyy = 103

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi, and thanks for the reply people.

    I will attempt your solutions when I load up query analyer.

    To answer Pam's question, I am trying to get the date from the datatime column (truncate the time part).

    Thanks

    Tryst

  • Thats sorted it.

    Thanks Jeff (and all).

    Tryst

  • You bet... thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply