Trigger to format date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger to format date

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 05:17:40

Hi All

I am trying to write a trigger to format a date field when ever data is imported into a table.

The dates in the table are in various formats - e.g. 2008/02/05 00:00, 02/05/2008 or 05/02/08. All I want to do is change the date to '05/02/2008'

I have got this far:
CREATE TRIGGER dbo.FORMAT_DATE
ON dbo.dialog
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

END
GO

Can anyone help as I have never really written triggers!

Cheers

Gopher

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 05:41:27

Why do you want to do this? Is the field holding date currently varchar type? You can easily do this format conversion while retrieveing from db using CONVERT() function.

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 06:03:26

Ok, have tried that with the following statement:

select distinct convert(datetime, date, 100) As NewDate from dialog

I think something is wrong as the date is coming out 2008-02-05 00:00:00.000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-08 : 06:05:58

1 Always use peroper DATETIME datatype to store dates
2 It is front end that should do formation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources