Date manipulation in SQL Server there are several functions, lets see same examples of that:
-- Example 1
select getdate()
these function returns current date time like 2009-07-26 14:55:03.210
-- Example 2
select GETDATE()+60
On these example we add 60 days and the result is 2009-09-24 14:56:40.210
-- Example 3
select DATEADD(M,2,getdate()) -- M month, y year, d day
The DateAdd function adds months, years or days to date supplied
So you can see that adding 2 month to the current date and the result is 2009-09-26 14:57:48.117
see the differente results between example 2 and 3 ( 2 days), because in the example 2 we add 60 days not 2 month.
-- Example 4
select DATEDIFF(DAY,(DATEADD(M,2,getdate())),(GETDATE()+60))
The function DateDiff calculates the difference between to dates and returns int, using the examples that you saw the result is -2
-- Example 5
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime
Set @datevar=GETDATE()
select @datevar
On this example you will saw how to format the date to British format
-- Example 6
select substring((convert(varchar(10),@datevar,127)),1,10)
result 2009-07-26
Converting date type to output varchar(10) using parameter 127 ( yyyy-mm-dd)
-- Example 7
select substring((convert(varchar(10),@datevar,103)),1,10)
result 26/07/2009
Converting date type to output varchar(10) using parameter 103 ( yyyy-mm-dd)
See more about convert function in http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
See more on Date on http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
Post that can help you avoid some problems:
See more on Date on http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
Post that can help you avoid some problems:
No response to “SQL Date Manipulation Functions and Examples”
Post a Comment