List of dates between two dates

Posted by Murugavelmsc under Sql Server on 2/27/2013 | Points: 10 | Views : 11392 | Status : [Member] | Replies : 9
Hi Experts,


I need a query to extract list of dates between two dates.
Only query (No function, No procedure)

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Ankitsrist on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
namaste,
you can use this query
select datediff(datepart, 'startdate','enddate')
where datepart means which part of date you want to extract, for eg. day, maonth, year

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

I need to display list of dates....

For eg. 2013-02-25 to 2013-02-28

2013-02-25
2013-02-26
2013-02-27
2013-02-28

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankitsrist on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
may be this will help u
SELECT date from leavereminder WHERE date BETWEEN '2013-02-25' AND '2013-02-28 '

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

what are the columns in leavereminder table.

Thanks,
Murugavel S




Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankitsrist on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
actualy leavereminder is my table name which i have used in my server studio, u can replace it with urs

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
for wat purpose....

as my understand,
we have to maintain table with list of dates is it rite?

if yes, how to maintain the table or who going to enter the date in the tables


Thanks,
Murugavel S


Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankitsrist on: 2/28/2013 [Member] Starter | Points: 25

Up
0
Down
yeah i can understand so my above query could help u
SELECT date from urtablename WHERE date BETWEEN '2013-02-25' AND '2013-02-28 '

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/28/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!
DECLARE @Start DATETIME, @End DATETIME

SELECT @Start='2013-02-25' , @End = '2013-02-28'

;WITH DateList
AS
(
SELECT @Start [Date]
UNION ALL
SELECT [Date] +1 FROM DateList WHERE [Date] <@End
)

SELECT CONVERT(VARCHAR(10), [Date],120) [List of Date] FROM DateList
List of Date

---------------
2013-02-25
2013-02-26
2013-02-27
2013-02-28


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prateekagra on: 3/22/2013 [Member] Starter | Points: 25

Up
0
Down
HI Murugavel

You can use the common type expression to retrieve the list of dates between two dates


WITH MYCTE1 AS
(
SELECT CAST('2013-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte1
WHERE DateValue + 1 < '2012-12-31'
)

SELECT DateValue
FROM mycte1
OPTION (MAXRECURSION 0)


rgds
prateek agarwal

Prateek Agarwal
DBA

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response