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.

triggername - name of the trigger
TABLENAME - name of the table that trigger is created on
COLUMNNAME - name of column in TABLENAME that will be updated with the output

I use this script to populate a HEADER column in a SQL2005 table and then I can export to a flat file and insert into an Oracle table. I also use this as a user defined function to post a readable date format to the web.

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

Rate

Share

Share

Rate