Select all dates from startdate and enddate

Question

Post reply

Select all dates from startdate and enddate

ginish-484247

Old Hand

Points: 375
More actions
August 30, 2007 at 5:59 pm

#176368

Hi,
How do we display all dates within startdate and enddate using sql query? For Example I want to display all dates within 9/5/2007(startdate) and 9/8/2007(enddate)
The output will be
9/5/2007
9/6/2007
9/7/2007
9/8/2007

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Answer in in the syntax of a query:

SELECT ...

FROM ???

Where have you "all dates" stored in?

_____________
Code for TallyGenerator

ginish-484247 Old Hand Points: 375 More actions · Answer 2

Hi

I have stored the start date and end date in a table named Reservation.

It has the following fields

firstNamevarchar50

lastNamevarchar50

telephonevarchar50

email varchar50

startdate datetime

enddate datetime

I do want to the select all dates within startdate and enddate.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You want to select all dates FROM WHERE?

Where are all dates stored?

_____________
Code for TallyGenerator

JMG SSC Eights! Points: 886 More actions · Answer 4

If you dont have a table containing the dates you are trying to find that lie between a date range, well... review your design.

You could do something like this though (not sure what format your dates are in).

Declare @StartDate datetime

Declare @EndDate datetime

set @StartDate = [insert datetime value here]

set @EndDate = [insert datetime value here]

Select distinct convert(varchar, StartDate, 103)

from Reservation

where convert(int, convert(varchar, StartDate, 112)) between

convert(int, convert(varchar, @StartDate, 112)) AND convert(int, convert(varchar, @EndDate, 112))

order by 1

This is selecting a list of start dates from your reservation table that lie between the range. If you dont want start dates, then you need to have a table that contains the dates that you want to select.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Since you want to select something that is NOT in your table (at least I assume you want to select ALL dates between certain boundaries, not only those where there is some reservation), you have to create a table that holds all dates.

Search these forums for "Dates table" or "Tally table", you'll find many posts where it is explained how to use them and how to create them.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

maybe this is what you may be looking for:

CREATE FUNCTION ufn_dba_Get_Daterange (@Startdate smalldatetime, @Enddate smalldatetime )

RETURNS @RangeDates TABLE (DateValue smalldatetime not null)

AS

BEGIN

DECLARE @Number int

Set @Number = datediff(d, CONVERT(varchar(10),@Startdate,121), CONVERT(varchar(10),@Enddate,121))

Declare @Looping int

Set @Looping = 0

Declare @wStartdate smalldatetime

Set @wStartdate = CONVERT(varchar(10),@Startdate,121)

While @Looping < @Number

begin

INSERT @RangeDates values(dateadd(d, @Looping, @wStartdate))

Set @Looping = @Looping + 1

END

RETURN

END

GO

select *

from dbo.ufn_dba_Get_Daterange('2007-08-01 13:00:20', '2007-08-20 14:30')

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ginish-484247 Old Hand Points: 375 More actions · Answer 7

Hi,

Yes.This is what Iam looking for.But How can I write this in an sql query so that I can use it for booking application which checks for available dates in a calender.ie For Example when a customer checks for available dates I can validate this is in the backend by checking all dates within startdate and enddate.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

SELECT *

FROM DateTableFunction() AS dtf

LEFT JOIN BookingTable AS bt ON bt.FromDate < dtf.Date AND bt.ToDate > dtf.Date

WHERE bt.FromDate IS NULL

Also, a more efficient date tabelfunction is found here

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

N 56°04'39.16"
E 12°55'05.25"

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

That's indeed a nice article Peter

Also ginish , this query is going to be executed frequently I think the first rule for every RDBMS goes : TELL YOUR SYSTEM WHAT YOU KNOW.

What I mean is : Build a date-table to use with it.

You may be better off than generating it dynamicaly (TVF) with every query.