Friday, February 6, 2015

Date and Time Functions in SQL Server

3:26 AM - By ajay desai 0


(i) GETDATE( ): - This function returns the current date and time of the computer in which an instance of SQL server is running.

For ex: - select GETDATE()                                                        


(ii) DAY(date): - this function returns the current day of the current date of the computer in which an instance of SQL server is running.

For ex: - select DAY(GETDATE())


 
(iii) MONTH(date): - this function returns the current month of the current date of the computer in which an instance of SQL server is running.

For ex: -  select MONTH(GETDATE())



(iv) YEAR(date): - this function returns the current year of the current date of the computer in which an instance of SQL server is running.

For ex: - select YEAR(GETDATE())


                                                                                                                         
(v) SYSDATETIME( ): - this function is similar to GETDATE( ), but it returns the current date and time of the computer with seconds value having a scale of 7.

For ex: - select SYSDATETIME()

 

(vi) SYSDATETIMEOFFSET( ): - this function returns the current date and time of the computer like SYSDATETIME( ) function along with time zone offset i.e. difference between IST (Indian Standard Time) and UTC (Coordinated Universal Time).

For ex: - select SYSDATETIMEOFFSET()

       


(vii) SYSUTCDATETIME( ): - this function returns the current date and time of the computer with seconds value having a scale of 7 according to UTC (Coordinated Universal Time).

For ex: - select SYSUTCDATETIME()

         


(viii) ISDATE( ): - this function returns ‘1’ if the expression is a valid date, else returns ‘0’.

(ix) SETDATEFORMAT( ): - This function sets the order of the month,day and year of a date in a particular session.

For ex: - set dateformat dmy

select ISDATE('11/04/2014')                                                                     


(x) DATENAME(datepart,date): - this function returns a string which represents a particular part of a specified date. Datepart is the parameter that specifies the part of the date to return.

For ex: - select DATENAME(m,GETDATE())

               
             

               select DATENAME(MM,GETDATE())

                 
                                           

               select DATENAME(yy,getdate())

               
             
                select DATENAME(YYYY,getdate())
                   
               
                 
                select datepart(dw,getdate())

                



(xi) DATEADD(datepart, number, date): - this function returns a new date time value by adding an interval (number) to the datepart of the given date.

For ex: - select DATEADD(dd,30,getdate())


                                                                                                                                                                                                             

select DATEADD(MM,2,getdate())



                                                                                                 

(xii) DATEDIFF(datepart, startdate, enddate): - this function returns the difference between the startdate and enddate according to the specified datepart format.

For ex: - select DATEDIFF(YY,'11/04/1984',GETDATE())
 


select DATEDIFF(YEAR,'10/24/1978','10/24/1998')
 





 select DATEDIFF(MM,'11/04/2004',GETDATE())

Tags:
About the Author

I am Azeheruddin Khan having more than 6 year experience in c#, Asp.net and ms sql.My work comprise of medium and enterprise level projects using asp.net and other Microsoft .net technologies. Please feel free to contact me for any queries via posting comments on my blog,i will try to reply as early as possible. Follow me @fresher2programmer
View all posts by admin →

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

adsense

© 2014 Fresher2Programmer. WP Theme-junkie converted by Bloggertheme9
Powered by Blogger.
back to top