Order the 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

SQL Server 2000 Forums

Transact-SQL (2000)

Order the dates

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-12-22 : 13:02:12

Hi All, I am trying to insert dates for the year into table. My problem is the dates are not ordered properly, and I am not sure how to do an update / order by instruction. ALl i want is the scrambled dates to go from Jan 1 - dec 31. Any idea how I can accomplish this? Here the code I am using to insert the dates:

Declare @i int
set @i=1
while @i<=270
Begin
insert into cashbalance (TheDay)
values(getdate() + @i)
set @i=@i+1
end

update cashbalance
set TheDay =CONVERT(VARCHAR(10),TheDay,120)

Any help is appreciated!
Dirwin

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-22 : 13:18:49

SQL doesn't support ordering the content of tables. ORDER BY is applied when returning results. e.g.,

SELECT MyDate FROM MyTable ORDER BY MyDate DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-23 : 01:20:53

>>update cashbalance
set TheDay =CONVERT(VARCHAR(10),TheDay,120)

Why do you want the conversion?
Dont convert it. When Selecting the query do Order by Date
What is the datatype of Theday?

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-23 : 03:37:55

Why are you using GETDATE(), when you aren't really interested in having the time portion in your data anyway? What about using something like this to populate a table with dates and time set to midnight?


CREATE TABLE #dates (dt DATETIME)

INSERT INTO #dates
 SELECT DATEADD(DAY,Number,'20051223')
   FROM master..spt_values
  WHERE type = 'P' AND NUMBER <=256

This will fill the table with 256 consecutive dates starting today. If you need more days you need to use your own table of numbers. Which isn't the worst thing anyway. If you need any meaningful order in your data you need to use ORDER BY in a SELECT statement.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-23 : 03:46:19

...if you need it more generic without a hard-coded starting date, modify it to


 SELECT DATEADD(DAY,Number,DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())))

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources