Beta
add text here
In this workspace we will onlyb explain the important things related to the functions and expression of date and time, And we will not touch on windows functions or aggregate functions we cover them in the previous course.
DATETIME and DATETIME2:
DATETIME and DATETIME2 are both data types in SQL Server that store date and time values. However, there are some differences between them that you should be aware of.
1. DATETIME has a date range from January 1, 1753 to December 31, 9999 and a time range from 00:00:00 to 23:59:59.997. It has a fractional seconds precision of 3 digits, which means it can store milliseconds up to 999. It also rounds the fractional seconds to increments of .000, .003, or .007 seconds. It uses 8 bytes of storage space and it is not compliant with the ANSI and ISO 8601 standards.
2. DATETIME2** has a larger date range from January 1, 0001 to December 31, 9999 and a time range from 00:00:00 to 23:59:59.9999999. It has a user-defined fractional seconds precision of up to 7 digits, which means it can store nanoseconds up to 9999999. It does not round the fractional seconds and it has higher accuracy. It uses less storage space depending on the precision (6 bytes for precision less than 3, 7 bytes for precision of 4 or 5, and 8 bytes for precision of 6 or 7). It is compliant with the ANSI and ISO 8601 standards.
To return the current date and time in local time as DATETIME object you must use GETDATE() function. SYSDATETIME() and SYSUTCDATETIME() return thr current date and time local or UTC but as DATETIME2 object.
Unknown integration
DataFrameavailable as
df
variable
SELECT GETDATE() AS Datetime_ltz,
SYSUTCDATETIME() AS datetime_utc;This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df1
variable
SELECT GETDATE() AS datetime_ltz,
-- rETURN TIMEZONE OFFEST FROM UTC
SYSDATETIMEOFFSET() AS time_zoneThis query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df2
variable
-- Define date use DECLARE
DECLARE
@SOMEdATE DATETIME2(3) = '2020-03-24'
-- Take year from part date
SELECT year(@SomeDate) AS year;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df3
variable
-- Define date and get month of date
DECLARE
@SomeDate DATETIME2(3) = '2020-03-24'
SELECT day(@SomeDate) As dayThis query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df7
variable
-- Define date and get day
DECLARE
@SomeDate DATETIME2(3) = '2020-03-24'This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Now we applaying this function in real data.We use table products from production database. Use column model_year. and order table from sales database
Unknown integration
DataFrameavailable as
df8
variable
SELECT TOP 5 *
FROM production.products;This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df4
variable
-- Select the columns you are want
SELECT product_id, product_name, model_year,
-- Part the date take year from model year column
YEAR(model_year) AS year
FROM production.products;This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df5
variable
-- Chose order_id, order_date, shipped_date
SELECT order_id, order_date,shipped_date,
-- Part date from order date column
MONTH(order_date) AS Month_date
FROM sales.ordersThis query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df6
variable
-- We will get the all days from shipped column
SELECT order_id,shipped_date,
DAY(shipped_date) AS day
FROM sales.orders;This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Parsing dates with datepart function
We can use DATEPART() and DATANAME() function to extracting date part from date.
The DATEPART() function in SQL Server returns an integer representing the specific part of the given date, such as year, month, day, hour, minute, second, etc. The DATENAME() function returns a character string that represents the specified part of the given date¹². For example:
You can use these functions to extract or manipulate parts of dates in various ways. For example, you can use them to calculate the difference between two dates, group data by date intervals, format dates in different styles, etc.