update a column with a specific format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update a column with a specific format

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-11 : 12:40:14

i need your help once again

ive got a table with a column "date" with default value or binding = (getdate())

and works fine when i insert a new row

example
name date
pipo 2010-10-30 12:14:51.273

and i need your help, because when i am updating the date column , im doing it by a calendar from asp.net,
and the result after choosing a date from the calendar and updating the table is

name date
pipo 2011-02-04 00:00:00.000

and i want to get something like this " check the hour"
name date
pipo 2011-02-04 11:11:00.000

because im having some troubles when the hour is 00:00:00.000

could i use anytrigger to get this hour "11:11:00.000" when i updating the column "date" or any other script that works for me?

thanks in advanced

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 12:48:30

create trigger xxxx on tbl for update
as
update tbl set date = getdate()
from tbl t
join inserted i
on t.pk = i.pk
join deleted d
on t.pk = d.pk
where i.date <> d.date

you can also add an if update(date) statement before this if you wish.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-11 : 15:32:27

hi nigelrivett, ill tell you that i hvae, im not a genius on sql, so if you can help me with my proper tables , i ll really apreciate that

my table

CREATE TABLE [dbo].[change_dates](
[name] [varchar](50) NULL,
[date] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[change_dates] ADD CONSTRAINT [DF_change_dates_date] DEFAULT (getdate()) FOR [date]
GO

my info in my table

name date
pepe 2011-11-11 15:20:51.077

if i update my table , example

update dbo.change_dates
set date= '07/18/2011'
where name = 'pepe'

my table is updated this

name date
pepe 2011-07-18 00:00:00.000

and i have several troubles because the hour got "00:00:00.000"

i would like to get my table this way

name date
pepe 2011-07-18 15:20:51.077

you notice the hour is different to "00:00:00.000" , and thats exactly what i need, but only when i am updating the table, when im inserting info idont have any problem beacuse the system gets the datetime

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:24:41

duplicate
please dont open multiple threads for same question

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167368&whichpage=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources