Number to Date.

create table testDate(DateNumber int)
go
insert into testDate
Select 20040101
Union
Select 20040201
Union
Select 20040301
Union
Select 20040401
go
Select DateNumber
from testDate
where convert(datetime,convert(varchar,DateNumber)) <
DateAdd(day,-45,getdate())
"Dylan" <anonymous@.discussions.microsoft.com> wrote in message
news:1848e01c41b1e$9d82cb60$a301280a@.phx.gbl...
> Hi,
> The accounts package that we use uses MSSQL for its
> database. One of the tables is for recording sales
> transactions. The due date field in this table is stored
> as INT size 4 in the YYYYMMDD format; i.e 20040405
> (today's date). I have been asked to create a script
> that will report all transactions that are x numbers past
> their due date from a specified date. So they might want
> to find out all transactions that will be 45 days overdue
> (past due date) as of the end of the month, for example.
> My problem is finding a way to convert this due_date
> filed to date format so that the date they enter can be
> deleted from it. Can anyone help?
> To recap:
> Convert 20040405 (INT) to 20040405 (YYYYMMDD - date)
> TIA,
> Dylan
Try following:
declare @.p_custom_date datetime
select *
from (select accnt_code
, convert(char(8),due_date) due_date, amount
from salfldgtel
WHERE ( ( ( ACCNT_CODE LIKE '51%')
AND ( ( ALLOCATION <> 'A' ) ) ) )
AND accnt_code <> '511534'
AND accnt_code <> '512088'
AND accnt_code <> '512165'
AND accnt_code <> '515124'
AND accnt_code <> '512199'
AND accnt_code <> '518020'
AND accnt_code <> '511538'
--if due_Date is integer
AND convert(datetime, cast(due_date as char(8))) between '19000101' and '20050101'
--and trunc(mod(due_date,1E4)*1E-2) between 1 and 12 CAN BE CHANGED TO
and round(((due_date % 10000) * .01),0,1) between 1 and 12
-- and mod(due_date,1E2) between 1 and 31)x CAN BE CHANGED TO
and (due_date % 100 ) between 1 and 31)x
--to_date(&p_custom_date,'DDMMYYYY') - x.due_date > 60
--above clause can be changed as follows, assuming you are looking for difference in two dates
in terms of number of days.
where datediff(dd,@.p_custom_date , x.due_date) > 60
order by accnt_code
dylan,
as you must have realized that,you can not straight away put the query that is working in
oracle into sql server. simply because the functions like trunc/mod and other expressions like
1E4/1E2 can not be accepted by sql server as it is. I've tried giving you closest possible
answer. Try referring to following url which will be more helpful to you.
Migrating Oracle Databases to SQL Server 2000
http://microsoft.com/sql/techinfo/de...rateOracle.asp
Vishal Parkar
vgparkar@.yahoo.co.in
|||Dear Vishal,
Thank you for your help. Not being a whiz at building
queries for DBs it took me a long time to build the
original query for Oracle. Trying to find the
corresponding statements for MS SQL was taking me a very
long time. You have helped me plenty.
Thanks again,
Dylan.
>--Original Message--
>Try following:
>declare @.p_custom_date datetime
>select *
>from (select accnt_code
>, convert(char(8),due_date) due_date, amount
>from salfldgtel
>WHERE ( ( ( ACCNT_CODE LIKE '51%')
>AND ( ( ALLOCATION <> 'A' ) ) ) )
>AND accnt_code <> '511534'
>AND accnt_code <> '512088'
>AND accnt_code <> '512165'
>AND accnt_code <> '515124'
>AND accnt_code <> '512199'
>AND accnt_code <> '518020'
>AND accnt_code <> '511538'
>--if due_Date is integer
>AND convert(datetime, cast(due_date as char(8)))
between '19000101' and '20050101'
>--and trunc(mod(due_date,1E4)*1E-2) between 1 and 12 CAN
BE CHANGED TO
>and round(((due_date % 10000) * .01),0,1) between 1 and
12
>-- and mod(due_date,1E2) between 1 and 31)x CAN BE
CHANGED TO
>and (due_date % 100 ) between 1 and 31)x
>--to_date(&p_custom_date,'DDMMYYYY') - x.due_date > 60
>--above clause can be changed as follows, assuming you
are looking for difference in two dates
>in terms of number of days.
>where datediff(dd,@.p_custom_date , x.due_date) > 60
>order by accnt_code
>
>dylan,
>as you must have realized that,you can not straight away
put the query that is working in
>oracle into sql server. simply because the functions
like trunc/mod and other expressions like
>1E4/1E2 can not be accepted by sql server as it is. I've
tried giving you closest possible
>answer. Try referring to following url which will be
more helpful to you.
>Migrating Oracle Databases to SQL Server 2000
>http://microsoft.com/sql/techinfo/de...t/2000/Migrate
Oracle.asp
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in
>
>.
>
|||Hi vishal,
declare @.p_custom_date is supposed to be a prompt for
users to enter a date. Is this how you pull this off in
SQL? When I run it in Query Analyzer it doesn't prompt
me and so it returns no rows. I manually enter a date in
the place of @.p_custom_date for the datediff clause it
seems to work.
Any ideads?
Thanks,
Dylan

>--Original Message--
>Try following:
>declare @.p_custom_date datetime
>select *
>from (select accnt_code
>, convert(char(8),due_date) due_date, amount
>from salfldgtel
>WHERE ( ( ( ACCNT_CODE LIKE '51%')
>AND ( ( ALLOCATION <> 'A' ) ) ) )
>AND accnt_code <> '511534'
>AND accnt_code <> '512088'
>AND accnt_code <> '512165'
>AND accnt_code <> '515124'
>AND accnt_code <> '512199'
>AND accnt_code <> '518020'
>AND accnt_code <> '511538'
>--if due_Date is integer
>AND convert(datetime, cast(due_date as char(8)))
between '19000101' and '20050101'
>--and trunc(mod(due_date,1E4)*1E-2) between 1 and 12 CAN
BE CHANGED TO
>and round(((due_date % 10000) * .01),0,1) between 1 and
12
>-- and mod(due_date,1E2) between 1 and 31)x CAN BE
CHANGED TO
>and (due_date % 100 ) between 1 and 31)x
>--to_date(&p_custom_date,'DDMMYYYY') - x.due_date > 60
>--above clause can be changed as follows, assuming you
are looking for difference in two dates
>in terms of number of days.
>where datediff(dd,@.p_custom_date , x.due_date) > 60
>order by accnt_code
>
>dylan,
>as you must have realized that,you can not straight away
put the query that is working in
>oracle into sql server. simply because the functions
like trunc/mod and other expressions like
>1E4/1E2 can not be accepted by sql server as it is. I've
tried giving you closest possible
>answer. Try referring to following url which will be
more helpful to you.
>Migrating Oracle Databases to SQL Server 2000
>http://microsoft.com/sql/techinfo/de...t/2000/Migrate
Oracle.asp
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in
>
>.
>
|||hi dylan,
Set value for this variable using SET .
Ex:
declare @.p_custom_date datetime
set @.p_custom_date = '20040101' --settting date value for variable @.p_custom_date
Vishal Parkar
vgparkar@.yahoo.co.in