Date Between - SQL Server 2008 - ASP.net

I have this query

WITH NumberedRows AS
(
    SELECT  Serial, 
            DateReceived, 
            DeviceLevel, 
            ROW_NUMBER() OVER (ORDER BY DateReceived) AS RowNumber
    FROM TBLReadings 
) 
SELECT *
FROM NumberedRows
WHERE RowNumber % 5 = 1 and Serial like '+447584996153'

I need to search between date - the last 7 days. I know the search..

WHERE DateReceived BETWEEN 'blahblah' and 'blahblah'

But what is the function? I have tried different formats to call the date specifically.

The current data retrieves the date in dd/mm/yyyy 00:00:00 format.

I do not know how to use the functions for date in the search correctly (I'm guessing this is why I cannot get any data when I search between dates as I just shows col name and no data)

Answers


Try Using DATEADD function to get data of last seven days:

WHERE DateReceived > DATEADD(day,-7, getutcdate())

or

WHERE DateReceived > DATEADD(day,-7, GETDATE())

If your dates are of DATE or DATETIME datatype, you can simply use:

WHERE DateReceived >= '20120601'            --- at or after the 1st of June
  AND DateReceived < '20120608'             --- and before the 8th of June

You can use Convert function for comparing dates which excludes time comparision.

Select Convert(varchar(12),GetDate(),112) this will return date in yyyyMMdd format

So in case of your query it can be WHERE Convert(varchar(12,DateReceived,112) BETWEEN Convert(varchar(12,DateFrom,112) and Convert(varchar(12,DateTo,112)


If you are planning on passing the date values as parameters to the query, then you can simply let .NET handle the stripping of the time:

WITH NumberedRows AS
(
    SELECT  Serial, 
            DateReceived, 
            DeviceLevel, 
            ROW_NUMBER() OVER (ORDER BY DateReceived) AS RowNumber
    FROM TBLReadings 
    Where DateReceived >= @Date1
        And DateReceived < DateAdd(d,8,@Date1)
) 
SELECT *
FROM NumberedRows
WHERE RowNumber % 5 = 1 and Serial like '+447584996153'

In your .NET code where you pass the date values, you would simply use the Date property of the DateTime object to only pass the date portion.:

myCommandVar.AddParameterWithValue( myDateVar.Date );

Another choice, if you always want to use today's date and you don't want to pass a parameter would be something like so:

Where DateReceived >= DateAdd( d, DateDiff( d, 0, CURRENT_TIMESTAMP ), 0 )
        And DateReceived < DateAdd( d, DateDiff( d, 0, CURRENT_TIMESTAMP ), 8 )

In this scenario, I'm stripping the time portion from the current date and time by calculating the number of days since date 0 and then adding that number of days to 0. Notice that in the second portion, I started from date 8.


Need Your Help

How to debug or/and trace the execution flow in C# with event handlers in it?

c# visual-studio-2010 debugging trace

I'm having a problem in tracing the execution flow in my c# code.With f10 or f11 i am able to trace just till the end of the main method.But in the runtime when an event is generated the function b...

Devise custom messages when validation fails

ruby-on-rails ruby ruby-on-rails-3 devise

I'm using devise as authentication engine in my app.