Tags

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


SQL Puzzle | Valid Dates > Today’s Date Puzzle

In this puzzle we have to find the valid dates from a column and return data from that column where the valid date is greater than today’s day. Please check the sample input and expected output.

Sample Input

a
0
Pawan
NULL
Dec 20 2017 6:58AM
Dec 19 2017 11:01AM

Expected Output

a
Dec 20 2017 6:58AM

Script

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

--

USE [master]
GO
/****** Object:  Table [dbo].[getDates]    Script Date: 12/19/2017 11:03:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[getDates]
(
	[a] [varchar](25) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[getDates] ([a]) VALUES (N'0')
GO
INSERT [dbo].[getDates] ([a]) VALUES (N'Vaibhav')
GO
INSERT [dbo].[getDates] ([a]) VALUES (N'')
GO
INSERT [dbo].[getDates] ([a]) VALUES (NULL)
GO
INSERT [dbo].[getDates] ([a]) VALUES (N'Dec 20 2017  6:58AM')
GO
INSERT [dbo].[getDates] ([a]) VALUES (N'Dec 19 2017 11:01AM')
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 * FROM getDates 
WHERE CASE WHEN ISDATE(a) = 1 AND a > GETDATE() THEN 1 ELSE 0 END = 1

--

Output-1

--

/*------------------------
 
SELECT * FROM getDates 
WHERE CASE WHEN ISDATE(a) = 1 AND a > GETDATE() THEN 1 ELSE 0 END = 1
------------------------*/
a
-------------------------
Dec 20 2017  6:58AM

(1 row(s) affected)


--

Solution – 2

--

SELECT * FROM getDates
WHERE IIF(ISDATE(a) = 1 AND a > GETDATE(), 1 , 0 ) = 1 

--

Output-2

--

/*------------------------
SELECT * FROM getDates
WHERE IIF(ISDATE(a) = 1 AND a > GETDATE(), 1 , 0 ) = 1 
------------------------*/
a
-------------------------
Dec 20 2017  6:58AM

(1 row(s) affected)

                                                                          
--

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