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:
