Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
Dealing with dates
Working with time data
Date and time date
Extracting dates and times
Doing arithmetic with dates
Converting date and time data
34. The CONVERT() function
Building date and time data from parts
Summary and review

Instruction

Great job! The CONVERT() function is similar to CAST(), but it takes more arguments:

CONVERT(newDataType, value, [style])

It's fairly straightforward. The first argument is the data type you want to have returned. The second is the value you want to convert. And the third, which is optional, is the time or date format you want. This style argument is an integer; for date and time formats, you can use one of the following codes:

Code Format
102 yyyy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yyyy
105 dd-mm-yyyy
107 Mon dd, yyyy
113 dd mon yyyy hh:mi:ss:mmm (24 hour format)
114 hh:mi:ss:mmm
(24 hour format)

There are more styles, including numeric values, that you can find in the SQL Server documentation.

Suppose you want to convert a date value from the ProducedDate column to the VARCHAR data type. And suppose you must have the date displayed as dd.mm.yyyy. This query will do the job:

SELECT
  ProducedDate,
  CONVERT(varchar, ProducedDate, 104) AS ConvertedDate
FROM Aircraft
WHERE Id = 3;

You'd see 2010-03-01 in the first column and 01.03.2010 in the second.

IMPORTANT: Date and time can only be formatted into string data types.

Exercise

Change the departure times of all routes to this format:

hh:mi:ss:mmm (24 hour)

Convert the DepartureTime column to a VARCHAR data type. Name the column ConvertedDepartureTime.

Stuck? Here's a hint!

Use CONVERT(data type, column, style).