Date and Time Functions: The following functions perform an
operation on a date and time input value and return a string, numeric, or date
and time value.
GETDATE(): Returns the current
date and time of the server in SQL Server standard internal format.
SELECT
GETDATE()
DAY(date): Returns an integer
representing the DAY of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
DAY(GETDATE())
SELECT
DAY(‘10/24/78’) OUTPUT:
24
MONTH(date): Returns an integer
representing the MONTH of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
MONTH(GETDATE())
SELECT
MONTH(‘10/24/78’) OUTPUT:
10
YEAR(date): Returns an integer
representing the YEAR of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
YEAR(GETDATE())
SELECT
YEAR(‘10/24/78’) OUTPUT:
1978
DATENAME(datepart, date): Returns
a character string representing the specified datepart of the specified date,
datepart is the parameter that specifies the part of the date to return. The
following table lists dateparts and abbreviations recognized by Sql Server:
Datepart
|
Abbreviations
|
year
|
yy, yyyy
|
quarter
|
qq, q
|
month
|
mm, m
|
dayofyear
|
dy, y
|
day
|
dd, d
|
week
|
wk, ww
|
weekday
|
dw
|
hour
|
hh
|
minute
|
mi, n
|
second
|
ss, s
|
millisecond
|
ms
|
SELECT DATENAME(mm,
‘10/24/78’) OUTPUT:
October
SELECT DATENAME(dd,
‘10/24/78’) OUTPUT: 10
DATEPART(datepart, date): This is
same as DATENAME function but the only difference is weekday (dw)
of DATEPART function returns a number that corresponds to the day of the week,
for example: Sunday = 1, Saturday = 7, where as in the case of DATENAME returns
the value in string format that is Sunday, Monday, … Saturday.
DATEADD(datepart, number, date):
Returns a new datetime value based on adding an interval to the
specified date, datepart is the value that has to be added and number is the
interval.
SELECT DATEADD(dd, 30, GETDATE())
–Adds 30 days to GETDATE().
SELECT DATEADD(mm, 16, GETDATE())
–Adds 16 months to GETDATE().
DATEDIFF(datepart, startdate,
enddate): Returns the difference between the start and end dates in the give
datepart format.
SELECT
DATEDIFF(yy, ‘10/24/78’, GETDATE())
GETUTCDATE()-Returns the datetime
value representing the current UTC time (Coordinated Universal Time or
Greenwich Mean Time).
SELECT
GETUTCDATE()
Conversion Functions: Explicitly
converts an expression of one data type to another. We has two conversion
functions CAST and CONVERT, both provide similar functionality.
Syntax for CAST:
CAST (
expression AS data_type [ (length ) ])
SELECT
CAST(10.6496 AS INT) OUTPUT:
10
SELECT CAST(10.3496847 AS money) OUTPUT:
10.3497
Syntax for CONVERT:
CONVERT ( data_type
[ ( length ) ] , expression [ , style ] )
SELECT
CONVERT(INT, 10.6496) OUTPUT:
10
SELECT
CONVERT(VARCHAR(50), GETDATE())
Style is an optional parameter
that can be used to specify a date format used to convert datetime or smalldatetime
data to character. When style is NULL, the result returned is also NULL.
Style can be used as following:
SELECT
CONVERT(VARCHAR(50), GETDATE(), 101)
SELECT
CONVERT(VARCHAR(50), GETDATE(), 102)
-Each style will give the output
of the date in a different format the default style it uses is 100. The style
values can be ranging between 100-114, 120, 121, 126, 127, 130 and 131 or 0 to
8, 10, 11, 12 and 14 in this case century part will not returned.
No comments:
Post a Comment