want only date portion from datetime datatype in UPDATE statement

Question

want only date portion from datetime datatype in UPDATE statement

raaj820

SSC Veteran

Points: 251
More actions
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

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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?

Adiga One Orange Chip Points: 27284 More actions · Answer 2

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

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 3

hi i want only date part from getdate function.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

tbeadle SSCommitted Points: 1635 More actions · Answer 5

[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]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

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)

nagaraju17 SSC Rookie Points: 37 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

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)