Your Ad Here

Friday, June 26, 2009

SQL - How to get date part only

So many programmers may face problems while getting date part of getdate() function or from variable datetime for MS-SQL Server. So here is one solution...

I developed the solution for date only from ‘getdate()’ function. Also this worked for me very effectively. Because I does not required to change the data type in the database or not required to write down different function for each time or not required to execute the same function for so many times.
I use this method.
1) Keep data type of column as ‘datetime’.
2)Keep its default value to ‘getdate()’.
3) Write trigger on that table as

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TABLE NAME]
FOR INSERT, UPDATE, DELETE
AS
UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)

It works very effectively.

0 comments: