display Full date values which falls under 2 input date values

Question

Post reply

display Full date values which falls under 2 input date values

sabarichandru

SSCommitted

Points: 1704
More actions
December 10, 2008 at 1:47 am

#77153

Dear all,
i need to display dates as a O/P which falls between two date inputs .
Eg:
input 1 : 01-12-2008
Input 2 : getdate()
O/P should be print like : 01-12-2008 .... till getdate .
can anyone help me out to sort out this issue .. but this o/p should be generate by select statement . without using functions or procedure .
pls help
Thanks in advance!
regards,
Sabari.C

Viewing 15 posts - 1 through 15 (of 19 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SELECT Date

FROM Calendar

WHERE Date BETWEEN @StartDate and @EndDate

_____________
Code for TallyGenerator

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Why do you want to get the dates only during run time? With SQL Server 2005 you can use Common Table Expression, but since you are working with SQL Server 2000, this is not possible. Unfortunately I’m not aware of any way to do it without using functions (or any other code) or dates table. The question is why not use any of them (function or dates table)?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sabarichandru SSCommitted Points: 1704 More actions · Answer 3

thnaks for your reply . but i should not use table fields . without using how can we derive the o/p .

Sergiy SSC Guru Points: 110208 More actions · Answer 4

sabarichandru (12/10/2008)
but i should not use table fields .

Why?

Is it kind of assessment?

_____________
Code for TallyGenerator

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Let me see if I understand you. You need to show a list of dates. Not only that the dates are important, it is also important that it will not be from a table, and it will not be calculated from a function. While I can understand that it is important that you’ll have the correct dates, I really can’t understand why it can’t be done using a table or a function. The user has to get the dates. The user shouldn’t know nor care how he got the dates. Can you try and explain your situation?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sabarichandru (12/10/2008)
Dear all,
i need to display dates as a O/P which falls between two date inputs .
Eg:
input 1 : 01-12-2008
Input 2 : getdate()
O/P should be print like : 01-12-2008 .... till getdate .
can anyone help me out to sort out this issue .. but this o/p should be generate by select statement . without using functions or procedure .
pls help
Thanks in advance!
regards,
Sabari.C

Do you want your dates all in one column or in one row?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sabarichandru SSCommitted Points: 1704 More actions · Answer 7

anyway with the help of table only i will derive date value .. but for test purpose they ask me not to use tables . directly they ask me specify the date values .

sabarichandru SSCommitted Points: 1704 More actions · Answer 8

sabarichandru

SSCommitted

Points: 1704

December 10, 2008 at 4:30 am

#910209

Hi chris,

i need a o/p in column .

sabarichandru SSCommitted Points: 1704 More actions · Answer 9

Hi sergy,

its not a assiginment .. part of my project . but they ask me not to use table fields directly u should specify the values to derive the date o/p .

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

sabarichandru (12/10/2008)
Hi chris,
i need a o/p in column .

This will do it:

DECLARE @InputDate DATETIME, @DayCount INT

SET @InputDate = '01-12-2008'

SET @DayCount = 1 + DATEDIFF(dd, @InputDate, GETDATE())

SELECT DATEADD(dd, 1-N.number, GETDATE()) AS MyDates

FROM Numbers N

WHERE N.number <= @DayCount

If you don't already have a tally, or numbers, table then you will need one for this code to work. Read about the tally table here:

http://www.sqlservercentral.com/articles/TSQL/62867/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 11

sabarichandru (12/10/2008)
Hi sergy,
its not a assiginment .. part of my project . but they ask me not to use table fields directly u should specify the values to derive the date o/p .

If it's your project why anybody tell you how to do things?

You should choose the most optimal solution, not the one someone prefers.

And reading from table is the one to choose.

Why? Because static table takes some amount of memory once when it's populated and then it's shared amongst all users need to read it.

Derived table is built in memory on fly, 1 table per user session.

In busy system amount of memory taken for all active sessions can kill the system performance.

Of course, you're not gonna discoverer it until system goes live and gets reasonable amount of users.

If you're a smart contractor you're gonna be long gone before this time.

And desperate owner of the project will face bankruptcy unless they can find someone like me who can apply appropriate database solutions.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Sure, but as others have already stated, a table source for this is the best way to go. The following code will solve the problem without a table, though. Courtesy of Itzek...

DECLARE @StartDate DATETIME,

@EndDate DATETIME

SELECT @StartDate = '01-12-2008',

@EndDate = DATEDIFF(dd,0,GETDATE())

;WITH

L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows

Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS N FROM L5)

SELECT N-1+@StartDate FROM Nums WHERE N <= DATEDIFF(dd,@StartDate,@EndDate)+1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)