show data only which DB dates are greater than Todays date

Question

Our db Table is PolicyNo PolStartDate PolicyEndDate 567 10-08-2017 10-08-2017 9087 10-08-2017 10-08-2017 9087 10-08-2017 09-08-2017 9087 09-08-2017 09-08-2017 5643 09-08-2017 09-08-2019 45 09-08-2017 09-08-2022 56 10-08-2017 10-08-2017 123 01-01-2015 01-01-2021 ** Here datatype of PolicyEndDate is string .i didnot change it. i want to show only which PolicyEndDate is greater than and equals to Today

Midhun T P · Answer

Hi Please try below query - SELECT * FROM table_name where convert ( date  PolicyEndDate 103) >= convert ( DATE getdate()103)

Manish Kumar · Answer

You have to change your column from string datetime . When converting policyenddate into date it will return date in yyyy-mm-dd format which is logically true for your condition means 10 August 2017 will convert to 8th October 2017 so all date are greater then today are returning . If you convert getdate() into string like below select convert(varchar(10)getdate()105) Sql server will treat this as string and comparsion (greater/less then) for string is not possible. see below query.... declare @tbl table (policyno int PolicyEndDate varchar(20) ) insert into @tbl select 567'10-08-2017' union all select 9087'09-08-2017' union all select 1567'10-08-2017' union all select 5643'09-08-2019' union all select 7854'09-07-2017' select policynoPolicyEndDate from @tbl --return all record due to conversion of PolicyEndDate in date and converts into yyyy-mm-dd where cast(PolicyEndDate as date)>=convert(varchar(10)getdate()105) select policynoPolicyEndDatecast(PolicyEndDate as date) from @tbl --retruns only 2 rows as getdate() is converted to string and returns only equal data.(no greater/less then) where PolicyEndDate>=convert(varchar(10)getdate()105)

Amit Gupta · Answer

As you are working with varchar instead of date datatype you need to convert it to specific datatype to get the output Select * from tablename where Convert(date PolicyEndDate 105) >= Convert(date getdate() 105) However I will strongly recommend you to use Date/datetime datatype instead of varchar it will help you to perform various task in future. Good Luck

aiswarjya sarengi · Answer

i know this query but this query is not show which i want. like this please check it PolicyNo PolStartDate PolicyEndDate 567 10-08-2017 10-08-2017 9087 10-08-2017 10-08-2017 5643 09-08-2017 09-08-2019 45 09-08-2017 09-08-2022 56 10-08-2017 10-08-2017 123 01-01-2015 01-01-2021

Amit Gupta · Answer

Its isn't too hard to try Select * from tablename where Cast (PolicyEndDate as datetime) >= getdate()

PolicyNo	PolStartDate	PolicyEndDate
567	10-08-2017	10-08-2017
9087	10-08-2017	10-08-2017
9087	10-08-2017	09-08-2017
9087	09-08-2017	09-08-2017
5643	09-08-2017	09-08-2019
45	09-08-2017	09-08-2022
56	10-08-2017	10-08-2017
123	01-01-2015	01-01-2021