Formatting dates with 1st, 2nd, 3rd, 4th titles

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

SQL Server Development (2000)

Formatting dates with 1st, 2nd, 3rd, 4th titles

Author

Topic

tribune
Posting Yak Master

105 Posts

Posted - 2004-04-26 : 18:56:35

I'm doing a while function to loop through a series of dates I am processing, and I need to spit out a description in in the following format if say I had a date range of April 22-25:

Reservation April 22nd
Reservation April 23rd
Reservation April 24th
Reservation April 25th

Any idea how to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 18:58:22

This should be handled on the front-end and not in SQL Server. Was your question related to the front-end or SQL Server?

Why do you need a loop?

Tara

tribune
Posting Yak Master

105 Posts

Posted - 2004-04-26 : 19:11:59

tara,

This must be done on the sql server. I am looping because I cannot figure out how to do the operation in a set. Here's my scenario...

I need to create a line-item invoice for a date range for a reservation. The sql server needs to insert a record into a line-item table for each date.

My date range is defined as:

declare @BeginningStay datetime
declare @EndingStay datetime

And I have two tables (Rates and RatesCustom). Rates contains default prices, and RatesCustom contains custom prices for special dates. If custom rates are found, they override the default rates. Each date of the interval is line-item entered into another table (Charges).

Rates
------
RateID (int) (pk)
UnitID (int) (ID for a property that we're calculating for)
DefaultMondayAmount (money)
DefaultTuesdayAmount (money)
....
DefaultSundayAmount (money)

RatesCustom
-----------
RateCustomID (int) (pk)
UnitID (int) (ID for a property that we're calculating for)
CustomDate (datetime)
Amount (money) (this value overrides the default above if record exists)

Charges
-------
ChargeID (int) (pk)
OrderID (int)
ChargeName (varchar) (format: "Reservation Month[rd][st][th][nd]")
Amount
LineItemNum

My current sproc looks like:

....

-- Assign local variables
select
@BeginningStay = BeginningStay,
@EndingStay = EndingStay,
@UnitID = UnitID
from Orders where OrderID = @OrderID

-- Calculate rent charges from reservation
declare @TempDate datetime
set @TempDate = @BeginningStay
while @TempDate <= @EndingStay
begin
-- Custom rates get priority
if exists(select @CustomAmount = Amount
from RatesCustom
where UnitID = @UnitID and CustomDate = @TempDate)

insert into Charges(blah blah) values (blah blah)

else

insert into Charges(blah blah) values (blah blah)

end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 19:18:42

[code]

SELECT 'Reservation ' + DATENAME(m, @BeginningStay) + ' ' + DATENAME(d, @BeginningStay) +
CASE
WHEN DATENAME(d, @BeginningStay) IN ('1', '21', '31') THEN 'st'
WHEN DATENAME(d, @BeginningStay) IN ('2', '22') THEN 'nd'
WHEN DATENAME(d, @BeginningStay) IN ('3', '23') THEN 'rd'
ELSE 'th'
END

[/code]

Tara

tribune
Posting Yak Master

105 Posts

Posted - 2004-04-26 : 19:25:29

thanks very much tara

I may ask you to take a look at my final procedure later if you dont mind...I think a while loop might take to long because my CustomRates table may have up to a quarter million records in it, and if the reservation is say three weeks long, SQL would have to search through 5.25 million records :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources