• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

SQL Server - Column Update

Hello All,

I have a SQL Server Question. I am not sure which the best way to do it is. Now the requirement:
Every time, a new row(s) is inserted to my table xyz, then there is column in that table, called [update time], that gets the time of update in the format below…

For example – server Table
col1      col2      col3      updateTime
dsas      sdads      sdad      12/04/2012 1.45 PM
dsas      sdads      sdad      12/04/2012 1.50 PM


Is this difficult to accomplish?

Thanks
R
0
Rayne
Asked:
Rayne
3 Solutions
 
RayneAuthor Commented:
Also, [updateTime] column has to be of any specific format for achieving this?
0
 
AnujSQL Server DBACommented:
Add a default constraint to UpdatedTime, so the default value will get inserted in to the updatedTime column when you did not supply the value for this column in your insert statement.

ALTER TABLE <TableName>
ADD CONSTRAINT DF_UpdatedTime_GetDate DEFAULT GETDATE() FOR UpdatedTime

You can also change the format if your UpdateTime is varchar column
ALTER TABLE <TableName>
ADD CONSTRAINT DF_UpdatedTime_GetDate DEFAULT CONVERT(VARCHAR,GETDATE(),101) FOR UpdatedTime
0
 
RayneAuthor Commented:
Perfect Anujnb,

That works but I am getting the date format as below:
Jan  2 2013  2:18AM

is it possible to get it like this?
01/02/2013 2:18AM

Thanks for your help so far :)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RehanYousafCommented:
For different time formats, you can try the following link: http://www.sql-server-helper.com/tips/date-formats.aspx
0
 
Pratima PharandeCommented:
try this for format as you want

select CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7)

Try this when you are retriving the data like

Select col1 , col2 , select CONVERT(VARCHAR(10), UpdateTime , 101) + ' ' + RIGHT(CONVERT(VARCHAR, UpdateTime , 100), 7)
0
 
RayneAuthor Commented:
Thank you All for your excellent 5 ***** help :)
For your effort.
Greatly appreciate it
0
 
AnujSQL Server DBACommented:
If this is a date time column I recommend you to Insert the date time value with default format and while retrieving the values convert to the required format. This method has the advantage that you can do date manipulations to the date time column with out the requirement of any conversion and of-course the performance adv also.

SELECT CONVERT(VARCHAR,GETDATE(),101)+' '+ LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7))

SELECT CONVERT(VARCHAR,UpdateTime(),101)+' '+ LTRIM(RIGHT(CONVERT(VARCHAR(20), UpdateTime, 100),7))
FROM <YourTable>
0
 
RayneAuthor Commented:
thank you Anuj :)

Sure I will keep that in mind
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now