dates between two dates - 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

General SQL Server Forums

New to SQL Server Programming

dates between two dates

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-26 : 07:42:27

Hi friends,
I need to display the dates in between two dates without using tables. two dates are today date and 10th date from today date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 08:08:09

SELECT DATEADD(dd,v.number,GETDATE())
FROM master..spt_values
WHERE v.number <=10
AND v.type='p'

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-26 : 08:51:57

hi visakh,

i need without storing the values in database

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 09:59:36

You dont need to store anything in database. spt_values is a count table within SQL. Just run the query and see the result it gives.

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 01:31:54

hi visakh,

I run the query using master database.It shows the error like this.

"The column prefix 'v' does not match with a table name or alias name used in the query."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 01:41:03

missed an alias:-

SELECT DATEADD(dd,v.number,GETDATE())
FROM master..spt_values v
WHERE v.number <=10
AND v.type='p'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 03:17:23

quote:
Originally posted by vidhya

Hi friends,
I need to display the dates in between two dates without using tables. two dates are today date and 10th date from today date.

Where do you want to display data?

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 08:47:20

hi visakh,
i got result.

I need to display the dates in between two dates without using tables. two dates are today date and before 10 days.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 08:50:27

quote:
Originally posted by vidhya

hi visakh,
i got result.

I need to display the dates in between two dates without using tables. two dates are today date and before 10 days.

You didnt answer to my question

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 08:54:33

hi Madhivanan,

Sorry for delay.
I need to display the dates in gridview.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 08:58:03

quote:
Originally posted by vidhya

hi Madhivanan,

Sorry for delay.
I need to display the dates in gridview.

Why dont you do this in your front end application?
Declare date variable and using while loop you can populate dates. Isnt it?

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 09:01:19

hi Madhivanan,

they asked me to fetch the values through database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:05:27

SELECT DATEADD(DAY, Number, DATEDIFF(DAY, '19000101', GETDATE())) AS theDate
FROM (SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS d
ORDER BY Number

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 09:07:05

quote:
Originally posted by vidhya

hi Madhivanan,

they asked me to fetch the values through database.

Whats wrong with Vishak's solution?
Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 09:35:51

hi peso,Madhivanan

I got result for that..

Another i need to display the dates between before 10 days from the date to today.
4/18/2008 to current date.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 09:40:53

From Peso's query

SELECT DATEADD(DAY, -Number, DATEDIFF(DAY, '19000101', '20080418')) AS theDate
FROM (SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS d
ORDER BY Number

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:41:40

[code]SELECT DATEADD(DAY, -Number, DATEDIFF(DAY, '19000101', GETDATE())) AS theDate
FROM (
SELECT 0 AS Number UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
) AS d
ORDER BY Number DESC[/code]

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

vidhya
Posting Yak Master

108 Posts

Posted - 2008-04-28 : 09:53:15

thanks for every one.i got solution for my question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources