Simple SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-27 : 11:05:27

Santosh writes "I have a table two field startdate and enddate. I want to get all the dates between startdate and enddate as rows in an SQL query.

eg startdate = 12/12/2001
enddate = 16/12/2001
i want the following result as
date
12/12/2001
13/12/2001
14/12/2001
15/12/2001
16/12/2001

Please help me out ASAP"

mfemenel
Professor Frink

1421 Posts

Posted - 2001-12-27 : 11:14:58

select * from tablename
where date between @startdate and @enddate

or

select * from tablename
where (date >=@startdate and date<=@enddate)

Mike
"A program is a device used to convert data into error messages."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 12:07:02

Or are you just looking for the date series? You might want to read this article on SQLTeam Answering a SQL Query Question from a Recruiter. The script that is included uses a technique to generate a date series between two dates. You also might want to search SQLTeam for other SEQUENCE related articles.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 12:09:23

I think the easiest way is to create a temp table to hold each date that you wish to display, then implement a loop that inserts the startdate into the table, then increment the startdate, and keep going as long as the startdate <= enddate. You could also implement this as a union, but you would have to use dynamic sql if you didn't know how many dates there were between the startdate and the enddate.

Does that help?

dKK

Rafiq
Starting Member

25 Posts

Posted - 2002-01-05 : 02:23:04

Hi,

It will work fine. try it.

select * from tablename
where (date >=@startdate and date<=@enddate)
Order by StartDate

Rafiq
------------------------------------
If you think, you can do anything...

Nazim
A custom title

1408 Posts

Posted - 2002-01-05 : 02:57:39

DaKarateKid! R u fascinated by Unions or what?.

i saw it here too

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11747

Hope i dont get a Kick! with your Expertise in Karate

Ajarn teach me some self Defence techniques

.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-05 : 21:49:24

quote:
Ajarn teach me some self Defence techniques.

Okay Nazim. Lesson #1: Learn not to provoke your opponent or teammate.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-05 : 22:14:02

Lesson #2 : Duck!

Damian

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-05 : 22:31:25

heh heh... that's good Merkin.

Followed shortly thereafter by RUN!

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 01/05/2002 22:32:00

Nazim
A custom title

1408 Posts

Posted - 2002-01-05 : 23:37:34

Am Running to the Mark

.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources