Change to readable date format

--  Trigger to insert date into table in the form of
--
--      01/01/00 12:00:00
--
--  instead of SQL standard date format
--
--      2000-01-01 12:00:00.000
--
--  This allows easily comparing to or updating based on
--  Oracle date formats or a readable format for the web.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[triggername] 
   ON  [dbo].[TABLENAME] 
   AFTER INSERT,UPDATE
AS 
BEGIN

SET NOCOUNT ON;

--  Get the short date.
DECLARE @RunDate AS CHAR(8)
SET @RunDate = CONVERT( CHAR(8), GetDate(), 10)

--  Get the short time.
DECLARE @RunTime AS CHAR(8)
SET @RunTime = CONVERT( CHAR(8), GetDate(), 8)

--  Combine the short date and time and add a space in between.
DECLARE @RunDateTime AS CHAR(17)
SET @RunDateTime = @RunDate + ' ' + @RunTime

--  Replace the hyphen with a slash.
DECLARE @ModRunDateTime AS CHAR(17)
SET @ModRunDateTime = replace(@RunDateTime, '-', '/')

--  Create the header string.
DECLARE @HeaderText AS CHAR(28)
SET @HeaderText  = 'Updated on ' + @ModRunDateTime

--  The header string can be output using PRINT @HEADERTEXT
--  or SELECT @HEADERTEXT to add to queries.
--
--  Output will look like this:  Updated on 01/01/00 12:00:00

    UPDATE TABLENAME
SET COLUMNNAME = @HeaderText 
--  WHERE * Simply addd a WHERE condition to only
--  update specific rows in the table.  Otherwise
--  all rows are updated.

END