Post reply

want only date portion from datetime datatype in UPDATE statement

  • January 14, 2008 at 12:32 pm

    #378013

    Hi,

    create table temp11 (datecolumn datetime)

    insert into temp11 values (getdate ())

    insert into temp11 values (getdate ())

    insert into temp11 values (getdate ())

    insert into temp11 values (getdate ())

    now when i am running this query,i am getting what i want...

    select convert (varchar, datecolumn,111) from temp11

    but when i am tyring to update in the temp11 table using the below query...

    update temp11

    set datecolumn = convert (varchar, datecolumn,111)

    i am getting date and time as well like...

    2008-01-14 00:00:00.000

    2008-01-14 00:00:00.000

    2008-01-14 00:00:00.000

    2008-01-14 00:00:00.000

    i only want the date portion in my updated new table.....

    any suggestions plzzzzzzzzz

  • January 14, 2008 at 1:21 pm

    #767839

    Don't confuse display with storage. In SQL 2005 and below - every datetime field has a data and a time component. You've already zeroed out the time component - the rest is a display issue (i.e. best left for the UI people to deal with).

    In 2008 you will have the option to use one of the "date only" datatypes I keep hearing about.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 18, 2008 at 2:55 am

    #779274

    If you are taking care of "Displaying" the result set, try using CONVERT.

    http://msdn2.microsoft.com/en-us/library/ms187928.aspx

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • April 10, 2008 at 3:47 am

    #800851

    hi i want only date part from getdate function.

    for example '2008/4/10' i want only date means 10 only

  • April 10, 2008 at 4:26 am

    #800862

    If you only want the day (ie from 2008/04/10 you just want 10) then look at either the datepart function or the day function. Both will do wahat you want.

    SELECT DATEPART(dd,getdate()) -- returns 10

    SELECT DAY(Getdate()) -- returns 10

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 10, 2008 at 9:45 am

    #801063

    [font="Arial"]Hello,

    I use convert(varchar(10), datecolumn, 121). This restricts the output from the convert function to 10 characters. The 121 is date format 2008-01-23 for example.

    So an update would be like

    update tableName

    set dateColumn = convert(varchar(10), getdate(), 121)

    Regards,

    Terry[/font]

  • April 10, 2008 at 6:57 pm

    #801244

    Still not going to help on a DATETIME column... A DATETIME column uses only the default system format when displaying data. If you want something else (should be done in a GUI, most times), you need to use the CONVERT function when you select the DATETIME data from the table.

    --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)
    Intro to Tally Tables and Functions

  • November 24, 2008 at 11:54 pm

    #902943

    HI ,

    any one help me out in update only the year part in datetime column

    example is :2008-10-01

    i want to update it 2007-10-09

    thanks in advance

    nagaraju

  • November 25, 2008 at 6:41 am

    #903084

    Yep... the DATEADD function will help in those areas. Check out Books Online for the syntax and parts of the date you can modify.

    --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)
    Intro to Tally Tables and Functions

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

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