C# .NET - how to retrieve all dates between from and to dates using datetime

Asked By mani on 28-Apr-12 01:09 AM

hi..how to retrieve all dates between from and to dates...using datetime..any help??

Anoop S replied to mani on 28-Apr-12 01:46 AM

If your database format is like this way 2012-04-28 00:00:00.000 and if you try to retrieve data between two date, if you didn't provide time stam then you will not get any data, so not to use BETWEEN, but to use the greater than or
equals operator (>=) and the less than operator (<).

for eg:

DECLARE @Orderdate datetime
SET @Orderdate = '2012/04/22'

SELECT *
FROM @Orders
WHERE Orderdate >= @Orderdate AND Orderdate < DATEADD(dd, 1, @Orderdate)

Chintan Vaghela replied to mani on 28-Apr-12 01:54 AM

Hello,

Make your QUERY as following way

DECLARE @FromDate as DateTIME = '2012-02-10 11:21:44.397'

DECLARE @ToDate as DateTIME = '2012-12-27 11:21:44.397'

SELECT * FROM tblDate as dt where CONVERT(datetime,dt.dtDate,111)

between CONVERT(datetime,@FromDate,111) and CONVERT(datetime,@ToDate,111)

Hope this helpful!

Thanks

kalpana aparnathi replied to mani on 28-Apr-12 06:35 AM

hi,

Use below example query for getting the result.

DECLARE @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = '20080101',@EndDate = '20080930'

SELECT DATEADD(day, z.num, @StartDate)

FROM (

SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6

CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7

CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8

CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9

CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10

) z

WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate)

ORDER BY z.num

Referance link:http://www.c-sharpcorner.com/Blogs/692/how-to-get-all-dates-between-date-range-in-sql-query.aspx

Regards,

dipa ahuja replied to mani on 28-Apr-12 09:26 AM

string q = "select city from table1 where Edate between('" + TextBox1.Text

+ "' AND '" + TextBox1.Text + "')";

Stored Procedure

Try this way:

ALTER PROCEDURE dbo.BetweenDates

(

@FromDate datetime,

@ToDate datetime

)

select * from table1 where orderDate between @FromDate and @ToDate

RETURN