Post reply

Adding zeros into date column

  • October 1, 2015 at 3:37 am

    #320983

    Hi,

    I have a date column (not datetime) in SQL Server and the dates are in the format yyyy-mm-dd

    I need to replace the date values with zero's so it looks like 0000-00-00

    I can currently get the current date in to the column like this:

    UPDATE TestTable_1

    set datecolumn = CONVERT(varchar(10), GETDATE(), 10)

    But instead of GETDATE() I need to enter the zero's like this: 0000-00-00

    How can I do this?

    Thanks.

  • October 1, 2015 at 3:48 am

    #1830680

    From what you're saying you want to replace all with 0s..

    UPDATE TestTable_1

    set datecolumn = '0000-00-00';

  • October 1, 2015 at 3:54 am

    #1830682

    Tried that. I get the following error:

    Conversion failed when converting date and/or time from character string.

  • October 1, 2015 at 4:21 am

    #1830686

    You cant do 0000-00-00 in any date field, if thats what you need I suggest CHAR(10)

    If you need it in a DATE datatype column the lowest value you can do is 0001-01-01 up to 9999-12-31

  • October 1, 2015 at 4:25 am

    #1830688

    Ok thanks.

  • October 1, 2015 at 4:41 am

    #1830691

    zedtec (10/1/2015)

    Hi,

    I have a date column (not datetime) in SQL Server and the dates are in the format yyyy-mm-dd

    I need to replace the date values with zero's so it looks like 0000-00-00

    I can currently get the current date in to the column like this:

    UPDATE TestTable_1

    set datecolumn = CONVERT(varchar(10), GETDATE(), 10)

    But instead of GETDATE() I need to enter the zero's like this: 0000-00-00

    How can I do this?

    Thanks.

    This is what NULL is for.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 1, 2015 at 8:46 am

    #1830802

    zedtec (10/1/2015)

    Hi,

    I have a date column (not datetime) in SQL Server and the dates are in the format yyyy-mm-dd

    I need to replace the date values with zero's so it looks like 0000-00-00

    I can currently get the current date in to the column like this:

    UPDATE TestTable_1

    set datecolumn = CONVERT(varchar(10), GETDATE(), 10)

    But instead of GETDATE() I need to enter the zero's like this: 0000-00-00

    How can I do this?

    Thanks.

    This should only be done during display time. Store NULLs in the column where this needs to be done so that you don't end up with all of the problems associated with storing dates/times as a VARCHAR.

    --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)

  • October 1, 2015 at 8:51 am

    #1830805

    Ok will do thanks.

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

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