query to fetch data according current date in asp.net C#

Haris Agha

con.Open();

SqlCommand cmd1 = new SqlCommand("SELECT [Description] FROM [tbl_Announcements] where Active = 1 AND ([ValidityDate] >= '" +DateTime.Today.ToLongDateString() + "')

cmd1.Connection = con;

SqlDataReader dr1 = cmd1.ExecuteReader();

while (dr1.Read())

{

k = dr1["Description"].ToString();

}

Label2.Text = k;

con.Close();

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
Accepted answer
hi haris what is the problem

your code is bellow where did not close double queted
Active = 1 AND ([ValidityDate] >= '" +DateTime.Today.ToLongDateString() + "')

end with double queted

Active = 1 AND ([ValidityDate] >= '" +DateTime.Today.ToLongDateString() + "')" ;
+1
Haris Agha8y ago
Thank you for the replies...
i figured it that the problem was in my query ... so i changed my query to ([ValidityDate] = '" +DateTime.Today.ToString("yyyy-MM-dd") + "')..., for it now works :)

Many thanks for taking out time ...
0
Raju Ahmed8y ago
Hi Haris Agha,

I've found some issues in your code, which should be change. Let discuss one by one.

Step 1:

Your Code: SqlCommand cmd1 = new SqlCommand("SELECT [Description] FROM [tbl_Announcements] where Active = 1 AND ([ValidityDate] >= '" +DateTime.Today.ToLongDateString() + "')

Correct: SqlCommand cmd1 = new SqlCommand("SELECT [Description] FROM [tbl_Announcements] where Active = 1 AND ([ValidityDate] >= '" + DateTime.Today.ToLongDateString() +" ' " ) ;

Mistake: Here you made two misktae.For clear understanging I marked those as red, one is you did not put double queted and second one is semicolon.

Step 2

C# DateTime.Today.ToLongDateString() Method returns data like 'Thursday, October 4, 2018' this. But SQL does not support this type of Date and SQL will give your error like following

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Instead of ToLongDateString(), you can try by specifying the
long date time formate like this one DateTime.Today.ToString("yyyy-MM-dd HH:mm;ss") .

Then your final Code should be looks like following:

SqlCommand cmd1 = new SqlCommand("SELECT [Description] FROM [tbl_Announcements] where Active = 1 AND ([ValidityDate] >= '" + DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss") + "'");

Happy Coding.
0