How can I get the current day,week,month in asp.net c#

Question

4.00/5 (2 votes)

See more:

Im using DateTime.Now(); in saving transaction in my program and its saves like this "2/21/2014 7:21:56 PM". I want to get all the transaction within the day. How can I get all the transaction within "2/21/2014".

C#

string strQuery = "Select Item_Purchased_Number,ProductID,ProductName,quantity,Branch, Date_Purchased from Customer_Purchased Where Branch = 'Manila'" + "'AND Date_Purchased = '" + DateTime.Now.AddHours(-24).ToString() ;

This is my query. Please help me. Badly needed. Advance thanks. :)

Posted 21-Feb-14 1:10am

DLSU-D Student

Updated 21-Feb-14 1:18am

Sampath Lokuge

v4

Add a Solution

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 · Answer 1 · 2014-02-21T01:21:00

Solution 1

OK, the first thing to notice is that DateTime.Now is a very specific time: it is literally the moment in time when you access the property. And when you subtract 24 hours from that, you get the same exact time, but yesterday. Since this time is accurate to milliseconds or less, it is very, very unlikely that your query will ever work, unless you are lucky and posts are made at the exact same time each day!
So, start by looking at DateTime.Now.Date[^] which discards the time part and goes back to midnight.
Then change your query to look at values greater than that:

C#

string strQuery = "Select Item_Purchased_Number,ProductID,ProductName,quantity,Branch, Date_Purchased from Customer_Purchased Where Branch = 'Manila'" + "'AND Date_Purchased >= '" + DateTime.Now.Date.ToString();

(or better still, use a parameterised query and don't convert to string - this can cause problems in some systems, since the string format can be very different depending on the PC system settings).

If you want specific days results, then the way I do it is to use the SQL DATEDIFF function:

C#

...WHERE DATEDIFF(d, Date_Purchased, " + DateTime.Now.ToString() + ")=0

Will find today, "=1" will be yesterday, and so forth.

Posted 21-Feb-14 1:21am

OriginalGriff

Comments

DLSU-D Student 25-Feb-14 11:20am

what if I will get the weekly or the monthly? DateTime.Now.Date.AddDays(-7) for weekly? Is this correct?

OriginalGriff 25-Feb-14 11:31am

Weekly is not that simple, because generally speaking weeks start on the same day of the week each time, so you have to work it out based on the current day of the week instead - you can't just subtract 7 and hope! :laugh:
Try:
DateTime now = DateTime.Now;
DateTime startOfWeek = now.AddDays(0-now.DayOfWeek);
For weeks started on Sunday.

DLSU-D Student 25-Feb-14 11:26am

@OriginalGriif there's an error "Incorrect syntax near '2'." What does this means?

OriginalGriff 25-Feb-14 11:33am

It means I forgot to enclose the date - I normally use parameterized queries fro everything to avoid SQL Injection.
...WHERE DATEDIFF(d, Date_Purchased, '" + DateTime.Now.ToString() + "')=0

DLSU-D Student 25-Feb-14 12:58pm

string strQuery = "Select Item_Purchased_Number,ProductID,ProductName,quantity,Branch, Date_Purchased from Customer_Purchased Where Branch = 'SM Dasmariñas'" + "'AND DATEDIFF(d, Date_Purchased, '" + DateTime.Now.ToString() +)=0);

Is this CORRECT? I get some errors. BTW Griff. Thanks for helping. :)

OriginalGriff 25-Feb-14 14:06pm

What errors? Apart from the lack of double quotes and single quote near the end...oh and a quote you don't need, and ... :laugh:
Look at it: and try matching things up. You should spot wheat I mean. If you don't, I'll give you a clue - but it's well worth looking yourself (remember the IDE colour codes things so it's pretty easy to see where the problems are, and when it compiles, you can use the debugger to see exactly what it generates)

Sampath Lokuge · Answer 2 · 2014-02-21T02:04:00

Please try is as below (SQL injection-proof).

Note: For simplicity,I extracted date part only.So complete it with your other where clause validations.

C#

var sqlCommand = new SqlCommand();
sqlCommand.CommandText = "Item_Purchased_Number,ProductID,ProductName,quantity,Branch, Date_Purchased from Customer_Purchased where Date_Purchased >= @DatePurchased";
sqlCommand.Parameters.AddWithValue("@DatePurchased", DateTime.Today);