Data Analyst and Technical Consultant

SQL Dates, Time and Strings

Here are some code snippets to help you get your head around how dates and times work in SQL.

DATEADD

Returns a date with the specified number added, or subtracted from part of that date

/* One month before the current system date */
select *
from
customers
where
dateadd(month, -1, getdate())

 

DATEDIFF

Returns the number of days, weeks, months or years between two dates.

/* One month before the current system date */ 
select 
datediff(day, purchasedate, getdate()) as timeelapsed 
from
salesrecords

 

UPPER using TRIM

In this example, TRIM removes any spaces in the string, adds a comma, adds the first name field and converts to upper case.  This technique can also be used with LOWER and PROPER depending on the desired result.

select upper(trim(LastName)) + ', ' + FirstName AS Name
from customers

 

LEFT and RIGHT

In this example, LEFT will return the first five characters from the name field

select left(Name, 5)
from Customers