increase date by 1 day

[wysiwygger]

Hi,

I'm trying to get a date value increased by 1 day.
The difficult part is the date is stored as a decimal datatype, e.g. 20070228. If I increase it by 1 using: SELECT MyDate + 1 would only give me 20070229 and not 20070301.

I have no influence on chaging the datatype.
Also if I try to convert my decimal value using:
SELECT CONVERT(datetime, MyDate + 1)
FROM cscrd

it comes of with an conversion error saying it can't convert this expression into datetime.
Any ideas?

Cheers

 

[AlexCuse]

Have a look in Books Online (SQL Server help) at DATEADD function.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Zathras]

I would probably write a function that would take the decimal value as an input and produce the desired decimal value as its output.

The function would convert the decimal to a string, split the string apart, build a date, use DATEADD, format the date to a yyyymmdd string and convert it to a decimal.

 

[AlexCuse]

I don't know that a function is necessary here, but it cetainly would make the queries easier to read.

wysiwygger - really what you need to do is store date as datetime data type if this is at all possible.

Otherwise you can use this horrible horrible code (placed in a function as zathras suggests):

select cast(convert(varchar(8), dateadd(day, 1, cast(cast(@decimal_column as varchar) as datetime)), 112) as decimal)

Ignorance of certain subjects is a great part of wisdom

 

[philhege]

ACCPAC dates, such as the dates in the CSCRD table, are date strings, not numbers. String manipulation/conversion of some sort is necessary. I have a couple of routines that query ACCPAC, but I wrote some nasty conversion code that would do more harm than good here.

Anyhoo, to get exchange rates for the past two weeks,

(@targetcur being the one to compare to the home rate)
select * from acc_cscrd
WHERE
HOMECUR = @home
AND SOURCECUR = @targetcur
and cast( RATEDATE as nvarchar(8)) between
convert( nvarchar(8), dateadd(day,-14,getdate()), 112)
and convert( nvarchar(8), getdate(), 112 )

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[wysiwygger]

Hi and thanks for your help!
I've got my statement now but I've created a monster!!!!
I'm not quite sure how to make this working with a function. All the attempts I made failed. Can you please give show me a way to make this work any better?
My monster now looks like this:

SELECT himdat.dbo.ebbudd.date, himdat.dbo.ebbudd.reference,
himdat.dbo.ebbudd.budamt1 AS originalorder,
himdat.dbo.ebbudd.budamt2 AS ordervariation,
himdat.dbo.ebbudd.budamt3 AS invoice,
himdat.dbo.ebbudd.optional3 AS description,
himdat.dbo.ebbudd.optional5 AS ordernumber,
himdat.dbo.ebbudd.optional6 AS status,
SUM(himsys.dbo.cscrd.rate) / COUNT(himsys.dbo.cscrd.rate)
AS exchangerate
FROM himdat.dbo.ebbudd, himsys.dbo.cscrd
WHERE himdat.dbo.ebbudd.field1 = 'P-05-012-00' AND
(himdat.dbo.ebbudd.optional6 = 'invoiced' OR
himdat.dbo.ebbudd.optional6 = 'notinvoiced' OR
himdat.dbo.ebbudd.optional6 = 'partlyinvoiced' OR
himdat.dbo.ebbudd.optional6 = 'payed') AND
himsys.dbo.cscrd.sourcecur = 'USD' AND
himsys.dbo.cscrd.ratedate BETWEEN
(SELECT cast(CONVERT(varchar(8), dateadd(day, - 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal)) AND
(SELECT cast(CONVERT(varchar(8), dateadd(day, + 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal))
GROUP BY himdat.dbo.ebbudd.date, himdat.dbo.ebbudd.reference,
himdat.dbo.ebbudd.budamt1, himdat.dbo.ebbudd.budamt2,
himdat.dbo.ebbudd.budamt3, himdat.dbo.ebbudd.optional3,
himdat.dbo.ebbudd.optional5, himdat.dbo.ebbudd.optional6

 

[philhege]

I don't think a function is necessary. You should write a stored procedure that accepts
the value for dbo.ebbudd.field1, the starting dateadd value, and the ending dateadd value.

In the where clause,

WHERE himdat.dbo.ebbudd.field1 = @field1val AND ...

BETWEEN
(SELECT cast(CONVERT(varchar(8), dateadd(day, @startdateoffset,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal)) AND
(SELECT cast(CONVERT(varchar(8), dateadd(day, @enddateoffset,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal))

HTH,


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Zathras]

Here is one way to do it:

CREATE FUNCTION dbo.AddDays (@RefDate decimal(8,0), @Days int)
RETURNS decimal(8,0)
WITH EXECUTE AS CALLER
AS
BEGIN
     RETURN(Convert(decimal(8,0),
              Convert(Char(8),
                DateAdd(day,@Days,Convert(DateTime,
                  Convert(Char(8),@RefDate))),112)))
END

select dbo.AddDays(20070228,-5),dbo.AddDays(20070228,5)

So that your example becomes something like this:

:
:
  himsys.dbo.cscrd.ratedate BETWEEN
     dbo.AddDays(himdat.dbo.ebbudd.date,-5) AND
     dbo.AddDays(himdat.dbo.ebbudd.date,5)
:
:

 

[wysiwygger]

Hi,

ok....I think I'm getting there. Before I try to put it all into a function a -hopefully last- minor change. I made 2 minor changes. The first one - the case statement - is working fine. If the currency is AUD the exchange rate is 1.
But because I don't have an exchange rate for AUD in my rates-table I was trying to exclude AUD with an IF-statement. Only if the currency is not AUD it should look for the rate date. I've tried it with if and case statements but no luck. Any chance you can help me out here?

SELECT himdat.dbo.ebbudd.date, himdat.dbo.ebbudd.reference,
himdat.dbo.ebbudd.budamt1 AS originalorder,
himdat.dbo.ebbudd.budamt2 AS ordervariation,
himdat.dbo.ebbudd.budamt3 AS invoice,
himdat.dbo.ebbudd.optional3 AS description,
himdat.dbo.ebbudd.optional5 AS ordernumber,
himdat.dbo.ebbudd.optional6 AS status,
exchangerate = CASE himdat.dbo.ebbudd.optional2 WHEN 'AUD'
THEN '1' ELSE SUM(himsys.dbo.cscrd.rate)
/ COUNT(himsys.dbo.cscrd.rate) END,
himdat.dbo.ebbudd.optional2 AS clientcurrency
FROM himdat.dbo.ebbudd, himsys.dbo.cscrd
WHERE himdat.dbo.ebbudd.field1 = 'P-05-012-00' AND
(optional6 = 'invoiced' OR
optional6 = 'notinvoiced' OR
optional6 = 'partlyinvoiced' OR
optional6 = 'payed') AND
IF (himdat.dbo.ebbudd.optional2 <> 'AUD')
himsys.dbo.cscrd.sourcecur = himdat.dbo.ebbudd.optional2 AND
himsys.dbo.cscrd.ratedate BETWEEN
(SELECT cast(CONVERT(varchar(8), dateadd(day, - 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal)) AND
(SELECT cast(CONVERT(varchar(8), dateadd(day, + 5,
cast(cast(himdat.dbo.ebbudd.date AS varchar)
AS datetime)), 112) AS decimal)) END
GROUP BY himdat.dbo.ebbudd.date, himdat.dbo.ebbudd.reference,
himdat.dbo.ebbudd.budamt1, himdat.dbo.ebbudd.budamt2,
himdat.dbo.ebbudd.budamt3, himdat.dbo.ebbudd.optional3,
himdat.dbo.ebbudd.optional5, himdat.dbo.ebbudd.optional6,
himdat.dbo.ebbudd.optional2

 

[philhege]

My stored procedure ignores the select statement entirely if the passed-in rate code is the native code:

if @Currency = 'USD'
begin
SET @ExchangeRate = 1.0
end
else

--...run the query and assign the exchange rate to @ExchangeRate



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.