When we are working with any programming language we need to work on the datetime. Every geographic location has different need of the date formats. Developer needs to take care of the datetime formats. The following statements are the most useful and common DateTime functions which are useful in SQL Server 2005/2008. I am giving with some example sql queries with the outputs.
Working With SQL datetime Data Type
SELECT
GETDATE(), -- 2014-07-23 00:48:46.663
CURRENT_TIMESTAMP, -- 2014-07-23 00:48:46.663
GETUTCDATE(), -- 2014-07-22 19:18:46.663
DAY(GETDATE()) , -- 23
MONTH(GETDATE()) , -- 7
YEAR(GETDATE()) -- 2014
--DATENAME
SELECT
DATENAME(year, getdate()) -- 2014
,DATENAME(month, getdate()) -- July
,DATENAME(day, getdate()) -- 23
,DATENAME(dayofyear, getdate()) -- 204
,DATENAME(weekday, getdate()) -- Wednesday
,DATENAME(hour, getdate()) -- 0
,DATENAME(minute, getdate()) -- 53
,DATENAME(second, getdate()) -- 57
,DATENAME(millisecond, getdate()) -- 140
--DATEPART
SELECT
DATEPART(year, getdate()) -- 2014
,DATEPART(month, getdate()) -- 7
,DATEPART(day, getdate()) -- 23
,DATEPART(dayofyear, getdate()) -- 204
,DATEPART(weekday, getdate()) -- 4
,DATEPART(hour, getdate()) -- 0
,DATEPART(minute, getdate()) -- 53
,DATEPART(second, getdate()) -- 57
,DATEPART(millisecond, getdate()) -- 140
-- DATEDIFF
DECLARE
@startdate DATETIME
SET
@startdate = '2014-08-15
10:10:10.100'
DECLARE
@enddate DATETIME
SET
@enddate = '2014-08-20
20:20:20.200';
SELECT
DATEDIFF(year,
@startdate, @enddate)
, -- 0
DATEDIFF(month,
@startdate, @enddate)
, -- 0
DATEDIFF(hour, @startdate, @enddate) , -- 130
DATEDIFF(minute, @startdate, @enddate) , -- 7810
DATEDIFF(second, @startdate, @enddate) , --468610
DATEDIFF(millisecond, @startdate, @enddate) --468610100
-- DATEADD
DECLARE
@datetime datetime
SET
@datetime = '2014-01-01
01:01:01.110'
SELECT DATEADD(quarter,4,@datetime) -- 2015-01-01
01:01:01.110
SELECT DATEADD(month,13,@datetime) -- 2015-02-01 01:01:01.110
SELECT DATEADD(dayofyear,365,@datetime) -- 2015-01-01
01:01:01.110
SELECT DATEADD(day,365,@datetime) -- 2015-01-01 01:01:01.110
SELECT DATEADD(week,5,@datetime)-- 2014-02-05 01:01:01.110
SELECT DATEADD(weekday,31,@datetime) -- 2014-02-01
01:01:01.110
SELECT DATEADD(hour,23,@datetime) -- 2014-01-02
00:01:01.110
SELECT DATEADD(minute,59,@datetime) -- 2014-01-01
02:00:01.110
SELECT DATEADD(second,59,@datetime) -- 2014-01-01
01:02:00.110
SELECT DATEADD(millisecond,1,@datetime) -- 2014-01-01
01:01:01.110
--ISDATE
SELECT ISDATE('2014-12-30 01:01:01.001') -- 1
SELECT ISDATE('12-30-2014 01:01:01.001') -- 1
SELECT ISDATE('30-12-2014 01:01:01.001') -- 0
SELECT ISDATE('2014/12/30 01:01:01.001') -- 1
SELECT ISDATE('12/30/2014 01:01:01.001') -- 1
SELECT ISDATE('30/12/2014 01:01:01.001') -- 0
SELECT ISDATE(NULL) -- 0
SELECT ISDATE('') -- 0
SELECT ISDATE('SRINU') -- 0
-- CONVERT
DECLARE
@INPUTDATE DATETIME
SET
@INPUTDATE='2014-12-30
12:59:59'
SELECT CONVERT(VARCHAR(10), @INPUTDATE, 105) , '[dd-mm-yyyy]'
--
30-12-2014 [dd-mm-yyyy]
SELECT CONVERT(VARCHAR(10), @INPUTDATE, 110), '[mm-dd-yyyy]'
--
12-30-2014 [mm-dd-yyyy]
SELECT CONVERT(VARCHAR(10), @INPUTDATE , 120), '[yyyy-mm-dd]'
--
2014-12-30 [YYYY-MM-DD]
SELECT CONVERT(VARCHAR(10), @INPUTDATE, 103) ,' [dd/mm/yyyy]'
--
30/12/2014 [dd/mm/yyyy]
SELECT CONVERT(VARCHAR(10), @INPUTDATE, 101) ,'[mm/dd/yyyy]'
--
12/30/2014 [mm/dd/yyyy]
SELECT CONVERT(VARCHAR(10), @INPUTDATE, 111) ,'[yyyy/mm/dd]'
--
2014/12/30 [yyyy/mm/dd]
SELECT CONVERT(VARCHAR(12),@INPUTDATE, 102),'[yyyy.mm.dd]'
--
2014.12.30 [yyyy.mm.dd]
SELECT CONVERT(VARCHAR(12),@INPUTDATE, 104),'[dd.mm.yyyy]'
--
30.12.2014 [dd.mm.yyyy]
SELECT CONVERT(VARCHAR(12), @INPUTDATE, 107) ,'[mmm dd, yyyy]'
-- Dec 30,
2014 [mmm dd, yyyy]
SELECT CONVERT(VARCHAR(8), @INPUTDATE, 108) , '[HH:mm:ss]'
-- 12:59:59 [HH:mm:ss]
SELECT CONVERT(VARCHAR(12), @INPUTDATE, 114) 'HH:mm:ss:fff'
-- 12:59:59:000
[HH:mm:ss:fff]
