bing
Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up
×
UPTO
50%
OFF!
Intellipaat
Intellipaat

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 are required. And the format – Defines the layout

For Example:  Table: Shopping

MerchandiseNameCostDate
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.

DATE FORMAT in SQL

The DATE_FORMAT ()

It is a function from the SQL server. 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 Date Format in SQL, the format of the input date should be similar to the column’s format in the store. Matching both date and time’s format is important.

  • 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

Some of the default 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 convert date format in 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

Date and Time Data Types

The Date format in SQL has datatypes, the SQL date and time data types are in the table below:

Data typeAccuracyStorage size (bytes)User-defined fractional second precisionTime zone offset
time100 nanoseconds3 to 5yesno
date1 day3no
smalldatetimen1 minute4nono
date time0.00333 second8nono
datetime2100 nanoseconds6 to 8yesno
datetimeoffset100 nanoseconds8 to 10yesyes

Date and Time Functions

FunctionSyntaxReturn data typeDeterministic
SYSDATETIMESYSDATETIME ()datetime2(7)No
SYSDATETIMEOFFSETSYSDATETIMEOFFSET ( )datetimeoffset(7)No
SYSUTCDATETIMESYSUTCDATETIME ( )datetime2(7)No

Lower-Precision System Date and Time Functions

FunctionSyntaxReturn data typeDeterministic
SYSDATETIMESYSDATETIME ()datetime2(7)No
SYSDATETIMEOFFSETSYSDATETIMEOFFSET ( )datetimeoffset(7)No
SYSUTCDATETIMESYSUTCDATETIME ( )datetime2(7)No

Functions to Get Date and Time Parts

FunctionSyntaxReturn data typeDeterministic
DATENAMEDATENAME ( datepart ,date )nvarcharNo
DATEPARTDATEPART ( datepart ,date )intNo
DAYDAY ( date )intYes
MONTHMONTH ( date )intYes
YEARYEAR ( date )intYes

Functions to Get Date and Time Values from Their Parts

FunctionSyntaxReturn data typeDeterministic
DATEFROMPARTSDATEFROMPARTS ( yearmonthday )dateDeterministic
DATETIME2FROMPARTS

DATETIME2FROMPARTS

yearmonthday,hourminutesecondsfractionsprecision )

datetime2

( precision )

Deterministic
DATETIMEFROMPARTS

DATETIMEFROMPARTS

yearmonthdayhour,minutesecondsmilliseconds )

datetimeDeterministic
DATETIMEOFFSETFROMPARTS

DATETIMEOFFSETFROMPARTS ( yearmonth,dayhourminuteseconds,

fractionshour_offset,minute_offsetprecision )

datetime

( precision )

Deterministic
SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS

yearmonth,dayhourminute )

smalldatetimeDeterministic
TIMEFROMPARTS

TIMEFROMPARTS

hourminuteseconds,fractionsprecision )

time

( precision )

Deterministic

Functions That Get Date and Time Difference

FunctionSyntaxReturn data typeDeterministic
DATEDIFFDATEDIFF ( datepart ,startdate , enddate )intDeterministic
DATEDIFF_BIGDATEDIFF_BIG ( datepart ,startdate , enddate )bigintDeterministic

Functions That Modify Date and Time Values

FunctionSyntaxReturn data typeDeterministic
DATEADDDATEADD (datepart , numberdate )The data type of the dateargumentDeterministic
EOMONTHEOMONTH (start_date [,month_to_add ] )Return type is the type ofstart_date or date.Deterministic
SWITCHOFFSETSWITCHOFFSET(DATETIMEOFFSETtime_zone)datetimeoffset with the fractional precision of theDATETIMEOFFSETDeterministic
TODATETIMEOFFSETTODATETIMEOFFSET (expression ,time_zone)datetimeoffset with the fractional precision of thedatetime argumentDeterministic

Functions That Set or Get Session Format

FunctionSyntaxDeterministic
@@DATEFIRST@@DATEFIRSTNondeterministic
SET DATEFIRSTSET DATEFIRST {number |@number_var }Not applicable
SET DATEFORMATSET DATEFORMAT {format |@format_var }Not applicable
@@LANGUAGE@@LANGUAGENot applicable
SET LANGUAGESET LANGUAGE { [ N ] language |@language_var }Not applicable
sp_helplanguagesp_helplanguage [ [ @language = ]language ]Not applicable

Functions That Proves the Date and Time Values

FunctionSyntaxReturn data typeDeterministic
ISDATEISDATE (expression )intYes

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 print outs of the Query Outputs
• Producing the Website Reports

This bring 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 the our coming up tutorial.

Previous Next

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on Date Format in SQL - SQL DateTime Format"

    Leave a Message

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.
    top

    Sales Offer

    Sign Up or Login to view the Free Date Format in SQL - SQL DateTime Format.