DATE FORMAT in SQL – SQL FORMAT ()

This FORMAT () function in the SQL is utilized for formatting a field in order to be brought into its displayable standard format.

The Syntax for the format function is mentioned below:

SELECT FORMAT (column_name, format) FROM table name;

Here, the column_name –  Tells about the ground where formatting is required. And the format – Defines the layout

For Example Table: Shopping

MerchandiseName Cost Date
SELECT MerchandiseName, Cost, FORMAT (Now (),'YYYY-MM-DD') AS PerDate FROM Merchandise;

The Above example chooses any item from the column named as MerchandiseName and cost for the particular day’s date from the Shopping table.

Get 100% Hike!

Master Most in Demand Skills Now !

DATE FORMAT in SQL

The DATE_FORMAT () returns a value formatted with the specified format. It is used for the locale-aware formatting of date/time and number values as strings. Let us understand more about it below:

The DATE_FORMAT ()

It is a function from the SQL server. The date format in SQL is used for displaying the time and date in several layouts and representations.

Syntax

DATE_FORMAT (date, format)

–Where date is a suitable date and  Format tells about the layout to be represented.

We have the perfect professional SQL certification course for you!

SQL Date Format Functions

In SQL, when working with a database, the format of the date in the  table must be similar to the input date. There are some default “date format” functions present in SQL. Following are some of the:

  • NOW ()  – Returns the current date and time
  • CURTIME() – Returns the current time
  • EXTRACT() – Returns a single part of a date/time
  • DATE_SUB() – Subtracts a specified time interval from a date
  • DATE_FORMAT() – Displays date/time data in different formats
  • DATEDIFF () – Returns the number of days between two dates
  • DATE_ADD() – Adds a specified time interval to a date
  • DATE() – Extracts the date part of a date or date/time expression
  • CURDATE() – Returns the current date

We also provide complete learning through our Database Certification, go through and enroll now.

SQL Date Functions

There are several SQL Date functions but not all are used mostly. There are certain SQL Date Functions that are used frequently. Some of those default SQL Date functions are:

  • GETDATE () – Returns the time between two dates
  • DATEADD () – Adds or subtracts a specified time interval from a date
  • CONVERT () – Displays date/time data in different formats. It converts date format into SQL
  • DATEPART () – Returns a single part of a date/time
  • DATEDIFF () – Returns the current date and time

Know the most common methods for executing function in sql by exploring our blog on how to run function in SQL

SQL Date Data Types

For storing date and time, the different data types are:

  • DATE – in YYYY-MM-DD format in SQL
  • YEAR – in YYYY or YY format in SQL
  • TIMESTAMP – in YYYY-MM-DD HH: MI:SS format in SQL
  • DATETIME – in YYYY-MM-DD HH: MI: SS format in SQL

Wish to get certified in SQL! Learn SQL from top SQL experts and excel in your career with intellipaat’s SQL certification in Bangalore.

Datetime Format In SQL

SQL Date and Time Data Types

The Date format types are in the table below:

Data type Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset
time 100 nanoseconds 3 to 5 yes no
date 1 day 3 no
smalldatetimen 1 minute 4 no no
date time 0.00333 second 8 no no
datetime2 100 nanoseconds 6 to 8 yes no
datetimeoffset 100 nanoseconds 8 to 10 yes yes

SQL Date and Time Functions

Function Syntax Return data type Deterministic
SYSDATETIME SYSDATETIME () datetime2(7) No
SYSDATETIMEOFFSET SYSDATETIMEOFFSET ( ) datetimeoffset(7) No
SYSUTCDATETIME SYSUTCDATETIME ( ) datetime2(7) No

Lower-Precision System Date and Time Functions

Function Syntax Return data type Deterministic
SYSDATETIME SYSDATETIME () datetime2(7) No
SYSDATETIMEOFFSET SYSDATETIMEOFFSET ( ) datetimeoffset(7) No
SYSUTCDATETIME SYSUTCDATETIME ( ) datetime2(7) No

Come to Intellipaat’s SQL Community if you have more queries on SQL!

Functions to Get Date and Time Parts in SQL

Function Syntax Return data type Deterministic
DATENAME DATENAME ( datepart ,date ) nvarchar No
DATEPART DATEPART ( datepart ,date ) int No
DAY DAY ( date ) int Yes
MONTH MONTH ( date ) int Yes
YEAR YEAR ( date ) int Yes

Functions to Get Date and Time Values from Their Parts

Function Syntax Return data type Deterministic
DATEFROMPARTS DATEFROMPARTS ( yearmonthday ) date Deterministic
DATETIME2FROMPARTS DATETIME2FROMPARTS
yearmonthday,hourminutesecondsfractionsprecision )
datetime2
( precision )
Deterministic
DATETIMEFROMPARTS DATETIMEFROMPARTS
yearmonthdayhour,minutesecondsmilliseconds )
datetime Deterministic
DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS ( yearmonth,dayhourminuteseconds,
fractionshour_offset,minute_offsetprecision )
datetime
( precision )
Deterministic
SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS
yearmonth,dayhourminute )
smalldatetime Deterministic
TIMEFROMPARTS TIMEFROMPARTS
hourminuteseconds,fractionsprecision )
time
( precision )
Deterministic

Career Transition

Got Job With Salary Hike | Process Developer to Sr. Software Engineer Career Transition
Got Multiple Job Offers with 100% Salary Hike | Best SQL Course | Intellipaat Career Transition
How To Switch Career From A Support Role To Oracle DBA Engineer | Got Job With 80% Salary Hike
How To Become A Database Expert From A Non Tech Background | Got Job With Salary Hike | Intellipaat
Got Job as Cloud DBA Just After completion of the Course | Intellipaat Career Transition
SQL Developer to Software Engineer Career Transition | Got Job with 120% Salary Hike | Intellipaat

Functions That Get Date and Time Difference

Function Syntax Return data type Deterministic
DATEDIFF DATEDIFF ( datepart ,startdate , enddate ) int Deterministic
DATEDIFF_BIG DATEDIFF_BIG ( datepart ,startdate , enddate ) bigint Deterministic

Functions That Modify Date and Time Values

Function Syntax Return data type Deterministic
DATEADD DATEADD (datepart , numberdate ) The data type of the date argument Deterministic
EOMONTH EOMONTH (start_date [,month_to_add ] ) Return type is the type ofstart_date or date. Deterministic
SWITCHOFFSET SWITCHOFFSET(DATETIMEOFFSETtime_zone) datetimeoffset with the fractional precision of theDATETIMEOFFSET Deterministic
TODATETIMEOFFSET TODATETIMEOFFSET (expression ,time_zone) datetimeoffset with the fractional precision of thedatetime argument Deterministic

Prepare yourself for the industry by going through this Top SQL Interview Questions and Answers!

Functions That Set or Get Session Format

Function Syntax Deterministic
@@DATEFIRST @@DATEFIRST Nondeterministic
SET DATEFIRST SET DATEFIRST {number |@number_var } Not applicable
SET DATEFORMAT SET DATEFORMAT {format |@format_var } Not applicable
@@LANGUAGE @@LANGUAGE Not applicable
SET LANGUAGE SET LANGUAGE { [ N ] language |@language_var } Not applicable
sp_helplanguage sp_helplanguage [ [ @language = ]language ] Not applicable

Functions That Proves the Date and Time Values

Function Syntax Return data type Deterministic
ISDATE ISDATE (expression ) int Yes

SQL Query formatter

It describes how we should format our SQL queries to create a final report. It includes
• Formatting the Columns in the Table
• expounding the Report with all the positioning and synopsis Lines
• Explaining folio and Report Topics and Sizes
• Storing and taking printouts of the Query Outputs
• Producing the Website Reports
This brings us to the end of this Date format in SQL. Meanwhile, there is a lot more you can do using SQL Date format which we will see in our coming up tutorial.

Course Schedule

Name Date Details
SQL Training 23 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 30 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 06 Apr 2024(Sat-Sun) Weekend Batch
View Details