Friday, 15 July 2011

Server Environment Server Environment Functions

  • SELECT GETDATE()

  • SELECT GETDATE() AS 'Date',DB_NAME() AS 'Database'

  • SELECT @@SERVERNAME 

  • SELECT   SERVERPROPERTY ('ServerName') AS ServerName,SERVERPROPERTY ('Edition') AS Edition, SERVERPROPERTY ('EngineEdition') AS EngineEdition, SERVERPROPERTY ('ProductLevel') AS ProductLevel

-- User Information Functions
  • SELECT   USER_NAME() AS 'User',  SUSER_SNAME() AS 'Login',  HOST_NAME() AS 'Workstation',  APP_NAME() AS 'Application'

---Date/Time Functions

  • SELECT DATENAME(YEAR, GETDATE()) AS YEAR

  • UPDATE Guide   SET DateOfBirth = '9/4/58'  WHERE lastName = 'Frank'

  • SELECT LastName,  DATENAME(yy,DateOfBirth) AS [Year],   DATENAME(mm,DateOfBirth) AS [Month],    DATENAME(dd,DateOfBirth) AS [Day],   DATENAME(WEEKDAY, DateOfBirth) AS BirthDay  FROM Guide  WHERE DateOfBirth IS NOT NULL

  • SELECT DATEPART(DayofYear, GETDATE()) AS DayCount
  • SELECT DATEPART(dw, GETDATE()) AS DayWeek

-- Date Math

  • SELECT DATEDIFF(yy,'2005/10/22', GETDATE()) AS MarriedYears,  DATEDIFF(dd,'2005/10/22', GETDATE()) AS MarriedDays

  • SELECT DATEADD(hh,100, GETDATE()) AS [100HoursFromNow]
  •  SELECT DATEDIFF(dd,getdate(),dateadd(yy,10,getdate()))