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”.

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