Tags

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


SQL Puzzle | Append 0 to month if month is < 10(Trick)

In this puzzle you have to find out the month number from the date column and if the month number is less then 9 append 0 before it.
Please check out the sample input values and sample expected output below.

Sample Input

Id Dates
1 2017-02-17 13:02:41.963
2 2016-11-09 13:02:41.963
3 2017-11-17 13:01:59.853
4 2017-09-17 13:01:59.853
5 2017-02-17 13:01:59.853

Expected Output

Id Dates Months
1 2017-02-17 13:02:41.963 02
2 2016-11-09 13:02:41.963 11
3 2017-11-17 13:01:59.853 11
4 2017-09-17 13:01:59.853 09
5 2017-02-17 13:01:59.853 02

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Months
(
	 Id INT
	,Dates DATETIME
)
GO

INSERT INTO Months VALUES
(1,GETDATE()),
(2,GETDATE()-100),
(3,'2017-11-17 13:01:59.853'),
(4,'2017-09-17 13:01:59.853'),
(5,'2017-02-17 13:01:59.853')
GO
 			 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

SELECT Id,Dates,RIGHT(CONCAT('0',MONTH(Dates)),2) Months
FROM Months

--

** Note ** – The trick is to add 0 before the month number and take 2 characters from the right hand side.

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements