Post reply

Selecting a year from an ntext field

  • November 22, 2007 at 5:10 am

    #70516

    I want to select the years from an ntext column. The date is in the format of '01/01/2000'. Please does anyone have any suggestions for this? I've tried to convert the column to a datetime datatype, however the SQL Server does not allow me to do this.

    Update:

    I've used to following

    SUBSTRING([Dob(2)], 7, 4)

    and this covers me for the moment.

  • November 22, 2007 at 5:53 am

    #753377

    Using substring is one solution, you could of course do a double cast, and use the datetime functions then:

    CAST(CAST(data AS VARCHAR(20)) AS datetime)

    Out of curiosity, is there really a need to use ntext as the return value?

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 22, 2007 at 5:56 am

    #753379

    No there isn't a need to use ntext. It just set that format when I copied it in.

  • November 22, 2007 at 5:59 am

    #753380

    Mark Green (11/22/2007)

    No there isn't a need to use ntext. It just set that format when I copied it in.

    In this case, i.e. if you have control over what the return type is, it is probably better to use datetime in the first place, or at least varchar, as this one can be cast to datetime later without double conversion.

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 22, 2007 at 11:02 am

    #753450

    Combination of PATINDEX (to find the string) and SUBSTRING (to extract the string) should do it.

    --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 5 posts - 1 through 4 (of 4 total)

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