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.
| Author |
Topic |
|
doubleotwo
Yak Posting Veteran
69 Posts |
 Posted - 2010-08-18 : 07:25:32
|
| DECLARE @date as datetime SELECT @date = (SELECT CAST((SUBSTRING(I.creatiedatum,5,4) + '-' + SUBSTRING(I.creatiedatum,3,2) + '-' + SUBSTRING(I.creatiedatum,1,2)) as datetime) FROM Inserted I) UPDATE tblklanten SET timecreated = @date i want to convert a field on insert...this field (creatiedatum) holds a date in folowing format DDMMYYYi want to convert it to a valid datetimestring for SQL and put it in timecreatedthx alot in advance :) |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-18 : 08:35:20
|
from books onlinequote:
SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2' ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';
--------------------keeping it simple... |
 |
|
|
doubleotwo
Yak Posting Veteran
69 Posts |
Posted - 2010-08-18 : 08:43:31
|
| okay thank you...but is my update statement correct ?i only want to change the inserted row ... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-18 : 09:56:56
|
not really, because if you get multiple row transaction in that single trigger event, you'll get an error...do an update with join (to the inserted table and the table you are updating)the example below is lifted from books onlinequote:
UPDATE Production.ProductSET ListPrice = ListPrice * 2FROM Production.Product AS pINNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID AND pv.VendorID = 51;GO
also, it's better to control the input via your application instead of doing it in a trigger... leave the formatting in the application layer and the storage in the database--------------------keeping it simple... |
|
|
|
|
|
|
|
|
noob trigger