Wednesday, May 30, 2007

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond.

SQL DATENAME Syntax
DATENAME ( datepart , date )

Example

SELECT GETDATE() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE())
SELECT DATENAME(yy, GETDATE())
SELECT DATENAME(yy, GETDATE())
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE())
SELECT DATENAME(qq, GETDATE())
SELECT DATENAME(q, GETDATE())
-It will return value = 2 (because 1 quarter equal to 3 month,Detail see below table)

Month

Quarter Value

January - March

1

April - June

2

July - September

3

October - December

4





SELECT DATENAME(month, GETDATE())
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE())
- It will return value = May

SELECT DATENAME(dayofyear, GETDATE())
SELECT DATENAME(dy, GETDATE())
SELECT DATENAME(y, GETDATE())
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE())
SELECT DATENAME(dd, GETDATE())
SELECT DATENAME(d, GETDATE())
- It will return value =
30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)