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)
Try Using DATEADD function to get data of last seven days:
WHERE DateReceived > DATEADD(day,-7, getutcdate())
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.