Daily Archives: April 13, 2015

SQL SERVER | How to get Decimal and Integer Part from Decimal Value

13 Monday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER | How to get Decimal and Integer Part from DecimalValue

Recently there was a scenario where we need to extract the decimal and the integer part for the decimal value. We have used below method.


--

/*

Easiest method to get decimal part and Integer part from Decimal Value

*/

DECLARE @DecimalValue AS NUMERIC(10,2) = 23.66
SELECT  @DecimalValue DecimalValue
       ,@DecimalValue - @DecimalValue % 1 DecimalPart
       ,@DecimalValue % 1 DecimalPart

--

Keep learning !, Please add a comment if you have a better idea in the mind.

We all need to learn.

http://MSBISkills.com

SQL SERVER | Find Number of Days in a Year

13 Monday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 2 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER | Find Number of Days in a Year

Ref – https://www.mathsisfun.com/leap-years.html

There are two types of Years – Leap year and Non Leap year. Lets first understand what are they-

Leap Year

  • A normal year has 365 days.
  • A Leap Year has 366 days (the extra day is the 29th of February).

How to know if a year will be a Leap Year:

Leap Years are any year that can be evenly divided by 4 (such as 2012, 2016, etc)
except if it can can be evenly divided by 100, then it isn’t (such as 2100, 2200, etc)
except if it can be evenly divided by 400, then it is (such as 2000, 2400)

Method 1 | Using Simple Maths & IIF/CASE Statements


/*

Find no of days in a Year - Leap Year / Non Leap Year
Every year has 365 days
If the year is leap then we have to add 1 ,
so logic will be 365 + add 1 if the year is leap

*/

DECLARE @YEAR AS INT = 2012
SELECT NofDays = 365 + ISNULL(CASE WHEN @YEAR%400=0 THEN 1
			 WHEN @YEAR%4=0 THEN 1
			 WHEN @YEAR%100=0 THEN 0
			 END,0)

GO

DECLARE @YEAR AS INT = 2012
SELECT NofDays = 365 +
 IIF( @YEAR%400 =0 , 1 , IIF(@YEAR%4 =0, 1, IIF(@YEAR%100 = 0,0,0)))
GO

--

Method 2 | Using Recursive CTE


--

/*

Method 2 | Using Recursive CTE
Note | We have to add OPTION (MAXRECURSION 366)
If the above option is not sepecified below error will come
"Msg 530, Level 16, State 1, Line 11
 The statement terminated. The maximum recursion 100 has been exhausted
 before statement completion."

*/

DECLARE @YEAR AS INT = 2015
;WITH CTE AS
(
	SELECT CAST(CONCAT('01/01/',@YEAR) AS DATETIME) y1
	UNION ALL
	SELECT DATEADD(d,1,y1) y1
        FROM CTE
        WHERE DATEADD(d,1,y1) <= CAST(CONCAT('12/31/',@YEAR) AS DATETIME)
)
SELECT COUNT(*) NoOfDays FROM CTE
OPTION (MAXRECURSION 366)

--

Method 3 | Using DateDiff


--

/*

Method 3 | Using DateDiff and Cast/Convert

*/

DECLARE @YEAR AS INT = 2012
SELECT
    DATEDIFF(d,CAST(CONCAT('01/01/',@YEAR) AS DATETIME)
    ,CAST(CONCAT('12/31/',@YEAR) AS DATETIME) + 1)

--

Method 4 | Using New DATEFROMPARTS & DATEPART


--

/*

Method 4 | Using New DATEFROMPARTS & DATEPART

*/

DECLARE @YEAR AS INT = 2012
SELECT NofDays = DATEPART(dy,DATEFROMPARTS(@YEAR,12,31))

--

SQL Server | Multiple ways to convert an Integer value to String

13 Monday Apr 2015

Posted by in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | Multiple ways to convert an Integer value to String | SQL Interview Question

This question has been asked to one of my collegue in SQL SERVER interview. I know this is not a new way but have we explored any other option apart from cast and convert for this.

Well, there are multiple ways to convert an integer value to a string value.

Some of them are-

  • Method 1 | Using CAST function
  • Method 2 | Using Convert function
  • Method 3 | Using STR function
  • Method 4 | Using Implicit Conversion

Method 1 | Using CAST function

1. It is one of the old methods to convert data from one datatype to another

2. It is the most preferred method of conversion from one datatype to another

Examples below-

--


DECLARE @Int AS INT = 25
SELECT CAST (@Int AS VARCHAR(2)) String
GO

--

Method 2 | Using Convert function

1. It is one of the old methods to convert data from one datatype to another

2. This method is widely used for Date type conversion for variety of formats.

Examples below-

--


DECLARE @Int AS INT = 25
SELECT CONVERT(VARCHAR(2),@Int) String
GO

--

Method 3 | Using STR function

1. It is one of the lesser known fact. Str function returns character data converted from numeric data

2. Note that although this is the workaround you should always use CAST/CONVERT in your development code

--


DECLARE @Int AS INT = 25
SELECT STR(25,2,1) String
GO

--

Method 4 | Using Implicit conversion

1. Here we are taking a Varchar Variable and assigning the int varible
to varchar variable.

2. Note that although this is the workaround you should always use CAST/CONVERT in your development code

--


DECLARE @Int AS INT = 25
DECLARE @Str AS VARCHAR(2) = ''
SET @Str = @Int
SELECT @Str String

--

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com