Microsoft SQL Insert Trigger to Get Current Datetime Value

This script gets the datetime value in this format MM/DD/YYYY:HR:MI:SSPM or AM. I could have used the CAST or CONVERT option but the did not reproduce the result that I wanted.  There are some option in CONVERT option but they converts into a 24hr format. Here is my trigger for insert event.

USE dwdata
GO
/*
-----------------------------------------------------------------------------
www.myw0.com, Version 1.0, Creation Date: 11/05/2014
Functionality: Generate Datetime values into a text field
-----------------------------------------------------------------------------
*/
if exists (select * from sysobjects where id = object_id('tr_iCopyStoreDatetimeValuesToTextField'))
drop trigger tr_iCopyStoreDatetimeValuesToTextField
GO
CREATE TRIGGER tr_iCopyStoreDatetimeValuesToTextField
ON [dbo].[SAMPLEEN]
FOR INSERT
AS
-- Variables
DECLARE @CurrDateUSA varchar(10),
@PMAMVal varchar(2),
@MinSecValue varchar(10),
@HrValue varchar(4),
@FinalDateTimeValue varchar(25)
-- Get the current date and time values in a specific format
SET @CurrDateUSA = CONVERT(VARCHAR(10), GETDATE(), 101) -- Date time format in xx/xx/xxxx
SET @HrValue = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 100), 13, 2) -- get the two digit hr
SET @MinSecValue = SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 108), LEN(CONVERT(VARCHAR(10), GETDATE(), 108)) - 5, 8) -- get the minute and second value
SET @PMAMVal = SUBSTRING(CONVERT(VARCHAR(10), CAST(GETDATE() AS TIME), 100), LEN(CONVERT(VARCHAR(10), CAST(GETDATE() AS TIME), 100)) - 1, 2) -- get the PM or AM
SET @FinalDateTimeValue = @CurrDateUSA + ':' + cast(replace(str(@HrValue, 2), ' ', '0') as char(2)) + @MinSecValue + @PMAMVal
-- Update the field in a table
UPDATE [dbo].[SAMPLEEN]
SET SUBJECT = @FinalDateTimeValue
WHERE DWDOCID = (SELECT DWDOCID FROM inserted)
GO

Results:

Results101

, ,

No comments yet.

Leave a Reply

*