We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
SQL Server - Column Update
Posted on 2013-01-02
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
- +1
8 Comments
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(),
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(),
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 :)
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 :)
For different time formats, you can try the following link: http://www.sql-server-helper.com/tips/date-formats.aspx
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)
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)
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(),
SELECT CONVERT(VARCHAR,UpdateTime
FROM <YourTable>
SELECT CONVERT(VARCHAR,GETDATE(),
SELECT CONVERT(VARCHAR,UpdateTime
FROM <YourTable>
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month5 days, 17 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.