SQL Date Manipulation Functions and Examples

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
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)

No response to “SQL Date Manipulation Functions and Examples”

Post a Comment