I want to compare system date with my SQL stored database date using query in ASP.NET C#

Question

0.00/5 (No votes)

See more:

i have stored data in SQL.there is a column "date" with 'DateTime' data type. now i want to compare system date with my database date using query. please help friends thanks in advance.

i have tried this code which are shown in its shows the total data of table. i want to show data current date to last 3 month of data.(thanks)

What I have tried:

SqlConnection con = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
con.Open();
try
{
DateTime maxdate = DateTime.Now.Date;
DateTime mindate = DateTime.Now.Date.AddMonths(-3);
SqlDataSource1.SelectCommand = "select * from general1 where date<='" + maxdate + "' AND date>='" + mindate + "'" ;

Posted 4-Nov-16 2:15am

navi G

Updated 4-Nov-16 2:46am

Add a Solution

Comments

[no name] 4-Nov-16 8:22am

"i want to show data current date to last 3 month of data", okay.... and?

navi G 4-Nov-16 8:28am

please see my code and tell me what is wrong with its.

[no name] 4-Nov-16 8:35am

I see your code and I can't tell you what's wrong with it. I can't run your code, I don't have access to your config file and I don't have access to your database. It is up to YOU to tell us what you see on your screen, or describe a problem or even ASK A QUESTION!

Sinisa Hajnal 4-Nov-16 8:27am

Use parametrized query and send UTC formatted data as parameters. Or use stored procedure and send datetime parameters directly. I commonly use UTC dates simply because I know what I'm sending and how I'm transforming it in the stored procedure (helps with compatibility)

Richard Deeming 4-Nov-16 10:38am

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-11-04T02:37:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
So try:

C#

DateTime maxdate = DateTime.Now.Date;
DateTime mindate = DateTime.Now.Date.AddMonths(-3);
SqlDataSource1.SelectCommand = "SELECT * FROM general1 WHERE [date] BETWEEN @MND AND @MXD";
SqlDataSource1.SelectCommand.Parameters.AddWithValue("@MND", mindate);
SqlDataSource1.SelectCommand.Parameters.AddWithValue("@MXD", maxdate);

#realJSOP · Accepted Answer · 2016-11-04T02:46:00

Something like this:

C#

SqlParameter[] parameters = new SqlParameter[]
{
    new SqlParameter("@minDate", minDate.Date),
    new SqlParameter("@maxDate", maxDate.Date)
};
string query = "select * from general1 where CONVERT(date, table.date) between @maxdate AND @mindate" ;
DataSet dataset = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(query, connetionstring))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddRange(parameters);
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            dataset = new DataSet();
            int rows = adapter.Fill(dataset);
        }
    }
}

I want to compare system date with my SQL stored database date using query in ASP.NET C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0