CODEWITHSUNDEEP
c#sql-serverdatetimesql-server-2000
Shmewnix
Shmewnix

Reputation: 1573

How to assign yesterday's date to a parameter C#

I have a C# program that will run as a windows scheduled task. This program will load, run a SQL Query, email the results stored in the dataset, and then close. I have everything except the using Yesterdays date.

Here is my current Query:

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number, @startdate AS Start, @enddate AS Today
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) AND (Paid_Out_Datetime BETWEEN @startdate AND @enddate)

Obviously I need to assign @startdate and @enddate at the time of the query. Since I will need 12AM to 1159PM that is the reason for the start and end. So for example. If I want to run the program today it would search yesterday (the 23rd) so @startdate would be assigned 7/22/12 00:00:00 and @enddate would be assigned 7/22/12 23:59:59...

Would it make more sense to do it in the query instead of the program? If so how would I change the query?

Upvotes: 3

Views: 1773

Answers (8)

FabianoLothor
FabianoLothor

Reputation: 2985

If you want to put in the query itself:

select (date_trunc('day', NOW()) - INTERVAL '1 day'); -- Yesterday Start
select (date_trunc('day', NOW()) - INTERVAL '1 second'); -- Yesterday End

Upvotes: 0

Void Ray
Void Ray

Reputation: 10219

You can also do this in SQL:

SELECT Store_Id, Paid_Out_Amount, Paid_Out_Comment, 
        Paid_Out_Datetime, Update_UserName, Till_Number, @startdate AS Start, @enddate AS Today
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) 
AND (Paid_Out_Datetime BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
    AND dateadd(second, -1, DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))))

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Don't use between when dealing with date intervals. It is much easier and safer to use >= and <.

Something like this will give you yesterday stuff without parameters.

SELECT Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number, @startdate AS Start, @enddate AS Today
FROM   Paid_Out_Tb
WHERE  Store_Id = 1929 AND 
       Paid_Out_Datetime >= dateadd(day, datediff(day, 0, getdate())-1, 0) and
       Paid_Out_Datetime < dateadd(day, datediff(day, 0, getdate()), 0)

Upvotes: 2

alexm
alexm

Reputation: 6882

This might also work, however with a performance drawback: 

 DateTime yesterday = DateTime.Today;
 Thread.Sleep(1000 * 60 * 60 * 24);

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1502786

Slight improvement on the existing answers, to always get the start of yesterday without having to call the DateTime constructor.

var todayStart = DateTime.Today;
var yesterdayStart = todayStart.AddDays(-1);
var yesterdayEnd = todayStart.AddSeconds(-1); // Ick...

Note that this will use the current system time zone for the meaning of "today" - are you sure that's what you want? You may wish to consider using:

var todayUtcStart = DateTime.UtcNow.Date;
...

It's a shame that BETWEEN treats the end point as inclusive - if it were equivalent to

start <= value && value < end

then you could just give two midnight values, which would be a lot clearer.

Also note that while it wouldn't make interacting with the database any cleaner, for other uses of dates and times, you may wish to consider my Noda Time library, which contains a data type specifically for representing a date (and another for "time of day") etc. The aim is certainly to clarify code using dates and times. If it doesn't, I've failed!

Upvotes: 5

Robert H
Robert H

Reputation: 11730

You should always use parameterized queries.

In order to support what you are looking for you can drop in yesterdays date into a variable and pass that variable to your parameter.

Upvotes: 0

Andomar
Andomar

Reputation: 238246

You could calculate the start of yesterday in C#:

var yesterday = DateTime.Now.AddDays(-1);
var startOfYesterday = new DateTime(yesterday.Year, yesterday.Day, yesterday.Month);
com.Parameters.AddWithValue("@enddate", startOfYesterday);

If you're using SQL Server 2008+, you can change the datatype from datetime to date. The later can only store dates, so you won't have to worry about the datetime part.

Upvotes: 4

HatSoft
HatSoft

Reputation: 11201

I assume you all looking for something this way

DateTime yesterdDaysDateTime = DateTime.Now.AddDays(-1);

Upvotes: 0

Related Questions