bing
Flat 10% & upto 50% off + Free additional Courses. Hurry up!

SQL Formatting

SQL FORMAT () Function

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:

SQL FORMAT () Syntax

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

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.

DATE_FORMAT  in SQL

The DATE_FORMAT ()

It is a function from the SQL server. It 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 Functions

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.
Some of the date functions are given below:
• 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
• 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
• YEAR
in YYYY or YY
• TIMESTAMP
in YYYY-MM-DD HH: MI:SS
• DATETIME
in YYYY-MM-DD HH: MI: SS

Datetime Format In SQL

  • Date and Time Data Types

The  SQL date and time data types are in the table below.

no

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
  • 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
CURRENT_TIMESTAMP CURRENT_TIMESTAMP datetime No
GETDATE GETDATE ( ) datetime No
GETUTCDATE GETUTCDATE ( ) datetime No

Functions to Get Date and Time Parts

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

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 dateargument 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

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

"0 Responses on SQL Formatting"

Leave a Message

Your email address will not be published.

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

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

top

Sales Offer

  • To avail this offer, enroll before 07th December 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
offer-june

Sign Up or Login to view the Free SQL Formatting.