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.
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
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
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
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 |
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 ( year, month, day ) |
date |
Deterministic |
| DATETIME2FROMPARTS |
DATETIME2FROMPARTS
( year, month, day,hour, minute, seconds, fractions, precision ) |
datetime2
( precision ) |
Deterministic |
| DATETIMEFROMPARTS |
DATETIMEFROMPARTS
( year, month, day, hour,minute, seconds, milliseconds ) |
datetime |
Deterministic |
| DATETIMEOFFSETFROMPARTS |
DATETIMEOFFSETFROMPARTS ( year, month,day, hour, minute, seconds,
fractions, hour_offset,minute_offset, precision ) |
datetime
( precision ) |
Deterministic |
| SMALLDATETIMEFROMPARTS |
SMALLDATETIMEFROMPARTS
( year, month,day, hour, minute ) |
smalldatetime |
Deterministic |
| TIMEFROMPARTS |
TIMEFROMPARTS
( hour, minute, seconds,fractions, precision ) |
time
( precision ) |
Deterministic |
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 , number, date ) |
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(DATETIMEOFFSET, time_zone) |
datetimeoffset with the fractional precision of theDATETIMEOFFSET |
Deterministic |
| TODATETIMEOFFSET |
TODATETIMEOFFSET (expression ,time_zone) |
datetimeoffset with the fractional precision of thedatetime argument |
Deterministic |
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.