SQL Tips And Tricks Examples

Example 1 Example 2 Example 3 Example 4 Example 5 Example 6 Example 7 Example 8 Example 9 Example 10 Example 11 Example 12 Example 13 Example 14 Example 15 Example 16 Example 17 Example 18 Example 19 Example 20 Example 21 Example 22 Example 23 Example 24 Example 25 Example 26 Example 27 Example 28 Example 29 Example 30 Example 31 Example 32 Example 33 Example 34 Example 35 Example 36 Example 37 Example 38 Example 39 Example 40 Example 41 Example 42 Example 43 Example 44 Example 45 Example 46 Example 47 Example 48 Example 49 Example 50 Example 51 Example 52 Example 53 Example 54 Example 55 Example 56 Example 57 Example 58 Example 59 Example 60 Example 61 Example 62 Example 63 Example 64 Example 65 Example 66 Example...

Read more »

Youngest Employee - SQL TOP

********************Youngest & Oldest Employee******************** To get the youngest / olderst employee, two different methods are shown here. 1. Using Max and Min Functions 2. Using Top in Select Statement '' Youngest Employee (Reference : AdventureWorks) Select Min(BirthDate) from HumanResources.Employee Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Asc Select TOP 1 * from HumanResources.Employee Order By BirthDate Asc Select * from Person.Contact where ContactID = (Select TOP 1 EmployeeID from HumanResources.Employee Order By BirthDate Asc) '' Oldest Employee (Reference : AdventureWorks) Select Max(BirthDate) from HumanResources.Employee Select TOP 1 BirthDate from HumanResources.Employee Order By 1 D...

Read more »

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it. If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression. Top 1 could return two or even more rows in case of a Tie Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent WITH TIES requires an ORDER BY clau...

Read more »

ANSI_NULLS - Equalling NULLs

********************Check For NULL******************** If you check for Null using <> or = operator, it might not return the results even if the table contains NULLs Select * from Person.Address where AddressLine2 = Null will not yield any results on the otherhand it is a good practice to get the intended value using the following Select * from Person.Address where AddressLine2 Is Null If you still want to use = or <> with Nulls then the following will work SET ANSI_NULLS OFF Select * from Person.Address where AddressLine2 = Null However, this feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this featu...

Read more »

GetAge - Get Age Using SQL Query

How to calculate Age using SQL Query DateDiff function can be used to get the Age from the birth date Select BirthDate, DateDiff(year, BirthDate, getdate()) as Age from HumanResources.Emplo...

Read more »

SQL Adding or Subtracting Dates

********************Adding or Subtracting Dates******************** If the requirement is to calculate the age of a person six months from now, dateadd function can help Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, 6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc subtracting dates is equally easy: Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, -6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 D...

Read more »

Universal Time or Greenwich Meridian Time (GMT)

Getting the Universal Time - GMT Select GetDate() as LocalTime Select GetUTCDate() as GMT_Time GetUTCDate Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time) Select DateDiff(second,GetDate(),GetUTCDate() ) as DifferenceinSec will give you the difference between GMT and your Local T...

Read more »