Date Manipulation

I originally posted this in the DTS forum, but then thought this particular
issue is more "programming" than "DTS" oriented...so I am not multi posting.
I've also changed the code slightly (found a couple of glaringly obvious
errors!)
I need to write a DTS package which is date oriented and I'm having problems
getting something coherent. I'm not a big guy in SQL Server, so please excus
e
my ignorance.
I need to get some sales information based on the previous day...however, if
the previous day was Monday, then I need to get the sales data for the
Friday. The where clause looks like this (and it's not
working...unsurprisingly).
/ ****************************************
*
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dd,getdate())) = 'MONDAY'
then
dateadd(dd,-3,getdate())
--and ididat < 20060320
else
dateadd(dd,-1,getdate())
--and ididat < 20060320
end
/ ****************************************
*
The error I get is
Arithmetic overflow error converting expression to data type datetime.
The IDIDAT field in the where clause is numeric and stored as yyyymmdd
Any ideas?
Thx for your helpHow odd, I've tried it with a table and no error at all, could you please so
kind to give us DDL code?
regards,
current location: alicante (es)
"Billy" wrote:

> I originally posted this in the DTS forum, but then thought this particula
r
> issue is more "programming" than "DTS" oriented...so I am not multi postin
g.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having proble
ms
> getting something coherent. I'm not a big guy in SQL Server, so please exc
use
> my ignorance.
> I need to get some sales information based on the previous day...however,
if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>|||Check to make sure that the ididat column contains a valid date for every
row.
SELECT * FROM <table> WHERE IsDate(ididat) = 0
will show you any rows with invalid dates.
Tom
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:3A5E2E82-42E2-4464-97FF-D38B206CC460@.microsoft.com...
>I originally posted this in the DTS forum, but then thought this particular
> issue is more "programming" than "DTS" oriented...so I am not multi
> posting.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having
> problems
> getting something coherent. I'm not a big guy in SQL Server, so please
> excuse
> my ignorance.
> I need to get some sales information based on the previous day...however,
> if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>|||I got it to work (to a fashion - I at least got rid of the error) by using
this syntax around the DATEADD features
****************************************
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
****************************************
***********
However, see my lastest post regarding getting the date in a USEABLE format!
Thx
"Enric" wrote:
> How odd, I've tried it with a table and no error at all, could you please
so
> kind to give us DDL code?
> regards,
> current location: alicante (es)
>
> "Billy" wrote:
>|||Got it working like so
WHERE
IDPERI > 200601 and cast(ididat as int) =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
CONVERT(char(8), DATEADD(dd,-3,GETDATE()), 112)
--and ididat < 20060320
else
CONVERT(char(8), DATEADD(dd,-1,GETDATE()), 112)
--and ididat < 20060320
end
Thx for posts.
"Billy" wrote:

> I originally posted this in the DTS forum, but then thought this particula
r
> issue is more "programming" than "DTS" oriented...so I am not multi postin
g.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having proble
ms
> getting something coherent. I'm not a big guy in SQL Server, so please exc
use
> my ignorance.
> I need to get some sales information based on the previous day...however,
if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>sql