Use date functions with WHERE and the datatype of date & time

Welcome People, Bots & Algo’s of LinkedIn. This is the Mondays ?Edition of The Analyst

We are using the sakila DB you can find the DB scripts at this github repo https://github.com/ivanceras/sakila

Use date functions with WHERE and the datatype of date & time

Date Time in SQL server is a date and time stamp from the SQL server system unless specified by the person entering the details

SQL SERVER does store timezone data using the datetimeoffset datatype??

?

Filter all last updates from 2006/2/15

So we create this query

SELECT * FROM
address
WHERE last_update = '2006-02-15'        

And this query returns nothing but you know for a fact there is data for the ?2006/2/15

When you do a address

SELECT TOP 10 * FROM        

you? these results

why Top 10 because you don’t want to create unnecessary load in the SQL SERVER

when you are querying a field which uses a datetime datatype you also need to add the time along with the 2006/2/15

or you could just turn the = to a > and the default time SQL SERVER assumes is midnight so 2006-2-15 00:00:00:000

?

SELECT * FROM
address
WHERE last_update > '2006-02-15'        

This will return every update greater than 2006-02-15

?

Filter based on year then month then Day ?

SELECT TOP 10* FROM
address
WHERE Year(last_update) = 15        

SELECT TOP 10* FROM
address
WHERE Month(last_update) = 02        

SELECT TOP 10* FROM
address
WHERE Day(last_update) = 15        

Find out the week and day of the week

SELECT DATEPART(week, '2006-02-15 '), DATEPART(weekday, '2006-02-15 ')        

It is the 7th week of 2006 and the days is a Wednesday.

Return date in a different format so the date in this database is very much so European and I will convert the date to a south African date time format

SELECT convert(varchar, last_update, 105)
FROM address        

Look up this article to find out more about date time conversion in sql server which is going to happen? https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN