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 Tp · 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 policyno,PolicyEndDate 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 policyno,PolicyEndDate,cast(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

Pooja Chowdhury · 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

show data only which DB dates are greater than Todays date

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)