Implicit conversion

When SQL wants to compare values, it checks if they are the same data type. In cases where they are not, it tried to convert from one type to another(IMPLICIT) and if this fails , you need to do an EXPLICIT (when the developer explicitly converts the data type.

Explicit conversion

USE CASES

SELECT 
    -- Transform the year part from the birthdate to a string
    first_name + ' ' + last_name + ' was born in ' + CAST(YEAR(birthdate) AS nvarchar) + '.' 
FROM voters;

SELECT 
    email,
    -- Convert birthdate to varchar show it like: "Mon dd,yyyy" 
    CONVERT(varchar, birthdate, 107) AS birthdate
FROM voters;

SELECT
    first_name,
    last_name,
    -- Convert birthdate to varchar(10) to show it as yy/mm/dd
    CONVERT(varchar(10), birthdate, 11) AS birthdate,
    gender,
    country,
    -- Convert the total_votes number to nvarchar
    'Voted ' + CAST(total_votes AS nvarchar) + ' times.' AS comments
FROM voters
WHERE country = 'Belgium'
    -- Select only the female voters
    AND gender = 'F'
    -- Select only people who voted more than 20 times
    AND total_votes > 20;

Functions returning date and time parts

Month returns the month number and not the month name.

Datename returns the month name.

Creating Date from DATEFROMPART

Getting part of a date

RECAP

SELECT 
    first_name,
    last_name,
       -- Extract the month number of the first vote
    DATEPART(MONTH,first_vote_date) AS first_vote_month1,
    -- Extract the month name of the first vote
    DATENAME(MONTH,first_vote_date) AS first_vote_month2,
    -- Extract the weekday number of the first vote
    DATEPART(WEEKDAY,first_vote_date) AS first_vote_weekday1,
    -- Extract the weekday name of the first vote
    DATENAME(WEEKDAY,first_vote_date) AS first_vote_weekday2
FROM voters;

SELECT 
    first_name,
    last_name,
    -- Select the year of the first vote
       YEAR(first_vote_date) AS first_vote_year, 
    -- Select the month of the first vote
    MONTH(first_vote_date) AS first_vote_month,
    -- Create a date as the start of the month of the first vote
    DATEFROMPARTS(YEAR(first_vote_date), MONTH(first_vote_date), 1) AS first_vote_starting_month
FROM voters;

Performing arithmetic operations on dates

RECAP

SELECT 
    first_name,
    birthdate,
    -- Add 18 years to the birthdate
    DATEADD(YEAR, 18, birthdate) AS eighteenth_birthday
  FROM voters;

SELECT
    -- Subtract 476 days from the current date
    DATEADD(DAY, -476, GETDATE()) AS date_476days_ago;

SELECT 
    -- Get the difference in weeks from 2019-01-01 until now
    DATEDIFF(WEEK, '2019-01-01', GETDATE()) AS weeks_passed;

Validating if an expression is a date

There is an anomaly in this function as it returns 0 for datetime2.

There are functions that can impact the result of ISDATE(), one of this function is:

SET DATEFORMAT returned 0 for the first date as it is in dmy format.

The first date is valid while the second is not.

If the language is changed to French, the first date becomes invalid and the second is valid:

Recap

DECLARE @date1 NVARCHAR(20) = '2018-30-12';

-- Set the date format and check if the variable is a date
SET DATEFORMAT ydm;
SELECT isdate(@date1) AS result;

DECLARE @date1 NVARCHAR(20) = '30.03.2019';

-- Set the correct language
SET LANGUAGE Dutch;
SELECT
    @date1 AS initial_date,
    -- Check that the date is valid
    ISDATE(@date1) AS is_valid,
    -- Select the name of the month
    datename(month,@date1) AS month_name;

DECLARE @date1 NVARCHAR(20) = '32/12/13';

-- Set the correct language
SET LANGUAGE Croatian;
SELECT
    @date1 AS initial_date,
    -- Check that the date is valid
    ISDATE(@date1) AS is_valid,
    -- Select the name of the month
    datename(month,@date1) AS month_name,
    -- Extract the year from the date
    datepart(year,@date1) AS year_name;

DECLARE @date1 NVARCHAR(20) = '12/18/55';

-- Set the correct language
SET LANGUAGE English;
SELECT
    @date1 AS initial_date,
    -- Check that the date is valid
    ISDATE(@date1) AS is_valid,
    -- Select the week day name
    DATENAME(WEEKDAY,@date1) AS week_day,
    -- Extract the year from the date
    DATEPART(YEAR, @date1) AS year_name;