This website completely moved to new platform. For latest content, visit www.programmingposts.com

Search this Site

10 Nov 2012

SQL SERVER DATE FUNCTIONS WITH EXAMPLE

SELECT GETDATE() /*RETURNS CURRENT DATE AND TIME*/
SELECT GETUTCDATE() /*RETURNS UTC DATE AND TIME*/
SELECT MONTH('2012/09/25') /*RETURNS INT NO,I.E MONTH NO=9*/
SELECT MONTH('2012/SEP/25') /*THIS ALSO RETURNS INT NO,i.e., MONTH NO=9*/
SELECT MONTH(GETDATE()) /*THIS ALSO RETURNS INT NO,i.e., MONTH NO=9*/
SELECT YEAR('2012/09/25') /*RETURNS THE YEAR*/
SELECT DAY('2012/09/25') /*RETURNS INT, THE DAY(DD) OF THE DATE*/
SELECT DAY(GETDATE()) /*RETURNS INT, THE DAY(DD) OF THE DATE*/
SELECT DATENAME(DD,'2012/09/25') /*RETURNS DAY 25*/
SELECT DATENAME(MM,'2012/09/25') /*RETURNS MONTGH IN WORDS i.e.,SEPTEMBER*/
SELECT DATENAME(YYYY,'2012/09/25') /*RETURNS THE YEAR 2012*/ /*DATEPART FOR DAY*/
SELECT DATEPART(DD,'2012/09/25')
SELECT DATEPART (DAY,'2012/09/25')
SELECT DATEPART(DAY,'2012/09/25')
SELECT DATEPART (DAY,GETDATE()) /*DATEPART FOR MONTH*/
SELECT DATEPART(MM,'2012/09/25')
SELECT DATEPART(M,'2012/09/25')
SELECT DATEPART(MONTH,'2012/09/25')
SELECT DATEPART(MM,GETDATE())
SELECT DATEPART(M,GETDATE())
SELECT DATEPART(MONTH,GETDATE()) /*DATEPART FOR YEAR*/
SELECT DATEPART(YY,'2012/09/25')
SELECT DATEPART (YYYY,'2012/09/25')
SELECT DATEPART (YEAR,'2012/09/25')
SELECT DATEPART(YY,GETDATE())
SELECT DATEPART (YYYY,GETDATE())
SELECT DATEPART (YEAR,GETDATE())
SELECT DATEPART (QQ,'2012/09/25') /*QUARTER PART OF THE YEAR*/
SELECT DATEPART (QQ,GETDATE()) /*QUARTER PART OF THE YEAR*/
SELECT DATEPART (DY,'2012/09/25') /*RETURNS DAY NO IN A YEAR*/
SELECT DATEPART(DY,GETDATE()) /*RETURNS DAY NO IN A YEAR*/
SELECT DATEPART(DW,GETDATE()) /*RETURNS DAY NO IN A WEEK*/
SELECT DATEPART(WW,'2012/09/25') /*RETURNSS WEEK OF THE YEAR*/
SELECT DATEPART(WK,'2012/09/25') /*RETURNS WEEK OF THE YEAR*/
SELECT DATEPART (HH,GETDATE()) /*RETURNS THE HOURS FROM THE CURRENT TIME*/
SELECT DATEPART(MM,GETDATE()) /*RETURNS MINUTES FROM THE CURRENT TIME*/
SELECT DATEPART (SS,GETDATE()) /*RETURNS SECONDS FROM THE CURRENT TIME*/
SELECT DATEPART(MS,GETDATE()) /*RETURNS MILLI SECONDS FROM THE CURRENT TIME*/
SELECT DATEADD(DAY,5,'2012/09/25') /*ADDS 5 DAYS TO CURRENT DATE*/
SELECT DATEADD(DAY,5,GETDATE()) /*ADDS 5 DAYS TO CURRENT DATE*/
SELECT DATEDIFF(DD,'1992/10/19',GETDATE()) /*DAYS DIFFERENCE*/
SELECT DATEDIFF(MM,'1992/10/19',GETDATE()) /*MONTH DIFFERENCE*/
SELECT DATEDIFF(YY,'1992/10/19',GETDATE()) /*YEAR DIFFERENCE*/
SELECT ISDATE('2012/09/25') /*CHECK FOR DATE FORMAT,RETURNS 1 FOR TRUE*/
SELECT ISDATE('2012/02/31') /*RETTURNS 0 FOR FALSE*/