DATETIME in build functions IN SQL SERVER to work with DateTime

There are several date time data types present in SQL server.

  • Time
  • date
  • datetime
  • smalldatetime
  • datetime2
  • datetimeoffset

 

Time

When we require only the time in the application we can make use of time data type. Time data type consumes 3-5 bytes of memory.

 

Date

When we want to store only the Date we can make use of Date data type. Date data type uses 3 bytes of memory.

 

DateTime

When we want both the date and time to be stored in database we can make use of datetime data type. DateTime data type uses 8 bytes of memory,

 

SmallDateTime

Small datetime stores the datetime. The time is based on 24 hours per day with seconds always: 00

 

DateTime2

Datetime2 is extension of datetime that can have larger date range

 

DateTimeOffset

DateTimeOffset is a datetime with UTC offset. YYYY-MM-DD hh:mm:ss[.nnnnnnn][+|-]hh:mm

 

UTC: Coordinated Universal Time is a standard that is used across the world based on which world regulates the clock and time. UTC is basically world standard time and based on this time other country time is calculated.

 

 

 

Date and Time Function in SQL server

 

Current TimeStamp:

Returns the db system timestamp without the db time offset. ANSI SQL equivalent to GETDATE

 

SELECT CURRENT_TIMESTAMP

 

Output: 2017-05-16 22:41:23.237

 

Getdate():

returns the current system date and time.

Syntax:

SELECT GETDATE() AS CURRENT_DATE_TIME

 

Output: 2017-05-16 22:43:38.420

 

Sysdatetime:

 

returns a date and time datetime(7) of db. It returns more fractional seconds precisions

 

select Sysdatetime()

 

Output: 2017-05-16 22:52:19.7178978

 

Sysdatetimeoffset()


returns the database system timestamp with database time zone offset

 

select Sysdatetimeoffset()

 

Output: 2017-05-16 22:53:12.6469252 +05:30

 

Sysutcdatetime()

returns system datetime without zone offset as datetime2. It returns more fractional seconds precisions.

 

select Sysutcdatetime()

 

Output: 2017-05-16 17:28:13.8181512

 

Sysdatetimeoffset()


returns system datetime with zone offset UTC and more fractional seconds precisions.

 

select Sysdatetimeoffset()

 

Output : 2017-05-16 22:58:48.2061181 +05:30

 

ISDate(varchar expression)

The isdate function determines whether the passed expression is a valid date or not?

It takes a character expression and returns the 1 if it’s a valid date and 0 if not as shown below:

 

SELECT ISDATE(GETDATE())

 

Output: 1

 

 

SELECT ISDATE(‘SAILLESH’)

 

Output: 0

 

ISDate function returns 0 for datetime2 or datetimeoffset as shown below:

 

select ISDATE(‘2017-05-16 22:33:33.6570000’)

 

Output: 0


select ISDATE(‘2017-05-16 22:33:33.6570000 +00:00’)

 

Output: 0

 

Day() returns the day number of the date of the specified date.

SELECT DAY(GETDATE())

Output: 17

 

 

DECLARE @DATE DATETIME

SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)

SELECT DAY(@DATE)

 

Output: 16

 

Month() function returns the month of the specified date. It takes a datetime expression as a parameter and returns an integer value corresponding to the month.

SELECT MONTH(GETDATE())

Output: 5

 

DECLARE @DATE DATETIME
SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)
SELECT MONTH(@DATE)

Output: 5

 

Year() function returns the year from the specified datetime passed in the expression.

SELECT YEAR(GETDATE())

 

Output: 2017

 

 

Datename() returns a string from the datetime expression based on the datepart from the datetime expression. Ex datepart can be a date, year, month.

 

Returning day from the datetime using DateName

 

SELECT DATENAME(DAY,GETDATE())

 

Output: 17

 

Retrieving weekday from the DateName method:

 

SELECT DATENAME(WEEKDAY,GETDATE())

 

Output: Wednesday

 

Retrieving name of the month from the DateName method:

SELECT DATENAME(MONTH,GETDATE())

 

Output: May

 

DatePart table

 

datepart

Abbreviations

year

yyyyyy

quarter

qqq

month

mmm

dayofyear

dyy

day

ddd

week

wkww

weekday

dw

hour

hh

minute

mi, n

second

sss

millisecond

ms

microsecond

mcs

nanosecond

ns

TZoffset

tz

ISO_WEEK

isowkisoww

 

So we can use the datepart shortcut as well in place of full datepart as shown below:

 

Retrieving year from the datetime using DateName:

 


SELECT DATENAME(yy,GETDATE())

 

Output: 2017

 

 

 

DatePart() methods returns the integer representing the specified DatePart type of the passed date.

select datepart(month,getdate())

Output: 5

 

select datepart(year,getdate())

Output: 2017

 

 

DateAdd() function increment specified value to the specified DatePart to the dateTime value as shown below:

 

select Dateadd(MONTH,1,GETDATE())

 

Output: 2017-06-30 22:02:55.437

 

select Dateadd(YEAR,1,GETDATE())

Output: 2018-05-31 22:12:07.897

 

Passing negative value in parameter will result in subtraction or decreasing the DatePart.

 

select getdate();

select Dateadd(YEAR,-5,GETDATE());

 

 

 

Output:


 

 

DateDiff() function returns difference between two datetime.

 

select Datediff(month,’1-1-2017′,GETDATE())

 

Output: 4

 

 

 


 

String Functions (Transact-SQL)

 

SQL server consists of two types of string functions:

  1. System defined String function
  2. User defined String Function

System functions are functions which are present in SQL server by default as shown below:


We can use system function based on our requirement. When we want to perform any aggregate than we use aggregate function like Max(), Avg() etc.

In this article I will be discussing about String Functions present in SQL Server.

 

ASCII(expression):

ASCII string function returns the ASCIII value of the first character (leftmost character) of the expression value as shown below:

 

SELECT ASCII(‘A’)

Output: 65

 

SELECT ASCII(‘a’)

Output: 97

 

 

 

It takes a char or varchar expression and returns an ASCII code value integer.

 

Char(integer expression)

 

Char() is an opposite of ASCII() function. Char() function converts a integer value to a Character. The Integer expression should be between 0-255.

 

select Char(65)

 

 

Output: A

 

select Char(97)

 

Output: a

 

It take a tiny int value which varies from 0-255 and returns a char(1).

 

 

 

 

 

 

 

 

 

 

 

 

 

LTRIM(Character Expression)

 

LTRIM() removes the white space on the left side of the given character expression.

In order to demonstrate the same lets taken an example of following:

 

print ‘ SAILLESH’

print LTRIM(‘ SAILLESH’)

 

Output:

 


 

 

RTRIM(Character Expression)

 

RTRIM() method is used to remove right side whitespaces of the given character expression.

 

print ‘SAILLESH ‘

print RTRIM(‘SAILLESH ‘)

 

Output:


 

Using LTRIM() AND RTRIM() to remove both left side and right side whitespaces from a character expression.

 

In business application there are scenarios where we need to display full name to the UI, due to not proper business logic names may get stored with whitespaces which may lead to UI discrepancy. So in order to solve the same we can make use LTRIM and RTRIM to remove the whitespace and to be the shown to the UI as shown below:

 

So we have a table which consists of only Names i.e. FirstName, MiddleName and LastName.

 


 

We can see due improper server side code values with whitespace are getting stored in db. So now in order to handle the same while showing to the UI we need to use RTRIM and LTRIM method to remove whitespaces.

 

 

select RTRIM(LTRIM(firstname)) + ‘ ‘ +

RTRIM(LTRIM(SecondName)) + ‘ ‘ +

RTRIM(LTRIM(LastName)) AS FULLNAME

FROM tblPersonDetails

 

Output:

 


 

 

 

UPPER (character expression)

 

UPPER() method returns the lowercase character expression to UPPER case character expression as shown below:

 

select UPPER(‘saillesh’)

Output:


 

I found Upper method useful when I want to apply a filter as shown below:

 

select FirstName from tblPersonDetails

where RTRIM(LTRIM(UPPER(SECONDNAME)))=’SINGH’;

 

Here I am changing the ColumnValue SecondName to uppercase and matching the same with ‘SINGH’.

 

It takes a varchar or nvarchar expression and returns a varchar or nvarchar expression converted to UpperCase.

 

 

 

 

 

Lower(character expression)

 

Lower() used to return the uppercase expression to lowercase character expression.

 

— Syntax for SQL Server

select LOWER(‘SAILLESH’)

 

 

 

Output:

 

 


It takes a character expression of type varchar or nvarchar and returns it to the lower case character expression.

 

REVERSE(Character Expression)

 

Reverse() function is used to reverse the character expression as shown below:

 

 

— Syntax for SQL Server

select REVERSE(‘SAILLESH’)

 

Output:


Len(Character Expression)

Len function returns the number of character present in the character Expression parameter. Len function is majorly used in stored procedure to apply the business logics.

 

Calculating length of each firstname and there total length.

 

select RTRIM(LTRIM(FirstName)),len(RTRIM(LTRIM(FirstName))) as LENGTH from tblPersonDetails

 

 

Output:

 


 

Len() ignores the right side whitespace at the end of the expression as shown below:

 

select LTRIM(FirstName),len(LTRIM(FirstName)) as LENGTH from tblPersonDetails

 

Output:


 

 

Left(characterExpression, length)

Left function returns the left most specified number of character from the character Expression.

 

Syntax: Select Left(characterExpression,NumberOfCharacterToBeReturned)

 

SELECT LEFT (‘SAILLES’,5)

Output: SAILL

 

 

Right(characterExpression, length)

 

Right method returns the right most characters from the specified number of character from the character expression.

 

Syntax: Select Right(characterExpression, NumberOfCharacterToBeReturned)

 

SELECT RIGHT (‘SAILLES’,5)

 

Output: ILLES

 

CharIndex()

CharIndex() method returns the starting point index of the specified character expression.

CharIndex method takes three parameters:

  1. Search expression: Expression to find
  2. Expression to be searched
  3. Start Location

 

Syntax:

 

select CHARINDEX(‘l’,’saillesh’,0)

 

Output:

4

 

SubStr()

Substr() method returns a Substring i.e. part of a character expression. SubStr() method takes a three parameters Expression to be SubStr, Starting Position, total length to be traversed.

 

Syntax:

 

SELECT SUBSTRING(‘SAILLESH’,1,5)

 

 

Showing all the email address with only domain name:

 


 

We have the above mentioned Table and we will show only the domain part of the email. In order to achieve the same let make use of CharIndex and SubString to achieve the same.

 

select SUBSTRING(email,CHARINDEX(‘@’,email,0)+1,LEN(email)-CHARINDEX(‘@’,EMAIL)) from TBLPERSON

 

 

Output:

 

UNION AND UNION ALL WHICH TO USE WHEN?

UNION and UNION ALL are used to combine the result of two or more SELECT queries. Sometimes in our enterprise application, we need to Union two Select queries to combine the result based on the business requirement.

UNION: When we use UNION or UNION ALL command all the selected columns need to be of same data type. The major difference between UNION and UNION ALL is that UNION returns the distinct records while UNION ALL returns the all rows returned from the respect SELECT queries. UNION internally does a SELECT DISTINCT on the returned result set which we will be seeing later.

Let’s take an example of our two tables i.e. ORDER and Orders

So now I want to retrieve CustomerId, StoreId, GrossTotal, and TotalOrderDiscount from both the table and combine the same into a single result set as shown below:

SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDER]
UNION
SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDERS]

Output:


Result Set

Now let’s check the Execution plan for the same and see how SQL server executes UNION command.


SQL SERVER performing DISTINCT Sort which is an expensive one Cost 64% time

Now if we use UNION ALL it will return all the records set resulted from each query as shown below:

SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDER]
UNION ALL
SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDERS]

Output:

You can see UNION ALL contains the Duplicate records. Let’s check the SQL plan for the same as shown below:

NO distinct overhead is performed in UNION ALL

We can clearly see that there is no distinct operation being performed in UNION ALL. So UNION ALL is quite fast as compared to UNION because there is no Distinct overhead.

Handling Null values with different in build functions in SQL SERVER

 

Sometimes there are scenarios where we have some column values as null, i.e. when we will retrieve those value from the database they will return as null. So in this scenario, if the column value has null we want null to be replaced by some other default value which can be achieved by using different functions in SQL server. This blog is basically focused on solving null values using in build function in SQL server.

So we have a table called TBLSTUDENT
which has following records:

 

 

As we can see above there are some students which don’t have their Passport. So now if we will be showing them in the UI or Export data they will be shown as Empty, But we want to replace them with NoPassport rather than showing as null. So in order to achieve the same, we can make use of IsNull(), Case & Coalesce() function.

 

  • ISNULL

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, ISNULL(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER
FROM TBLSTUDENT

 

Output:

 


We can see how easily we have successfully changed the null value to our default value as ‘NO PASSPORT’.

 

 

  • COALESCE

Coalesce function is used to return the first non-null value from the passed expression. We can pass multiple expression to it as shown below:

Example:

SELECT COALESCE(null,null,null,’sasd’)

 

Using COALESCE function to solve our problem:

 

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, COALESCE(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER FROM TBLSTUDENT

 

Output:

 

 

  • CASE

 

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO,

CASE WHEN

PASSPORTNUMBER IS NULL THEN

‘NO PASSPORT’

ELSE

PASSPORTNUMBER

END AS PASSPORTNUMBER

FROM TBLSTUDENT

 

Output:


Happy Learning..