Convert Date to String

Hi all,
I have a bit of a task...
We are hitting an old VMS system with a sql statement through an
Intersystems Cache ODBC driver (very old - not the most recent driver - not
upgrade-able).
I'm firing the query through a DTS.
In leighmans (sp?) I seem to have to use very "simple" queries in order to
not create an error message - anything a bit "clever" seems to kill it.
I need to query the database using a date, however, the company that wrote
the database in the first place stored the date as a string in this format:
ccyymmdd (20060424 for example).
I need to get the current date - less 2 days - and then convert it to that
format in order to use it in the sql statement. My problem seems to be that
I dont get the ' ' around the value and therefore it never works - throws a
very unhelpful error message up (just a long number with a minus sign in
front of it).
Can anyone shed any light on this at all for me - I appreciate the above is
a bit vague...
I've tried using parameters but that didn't work either...
If I hard code in the date it works fine - ie... WHERE their_date_column =
'20060424'
Any help would be appreciated.
Regards
RobCan you use the following code without it 'killing' it?
Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
HTH
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> Hi all,
> I have a bit of a task...
> We are hitting an old VMS system with a sql statement through an
> Intersystems Cache ODBC driver (very old - not the most recent driver - no
t
> upgrade-able).
> I'm firing the query through a DTS.
> In leighmans (sp?) I seem to have to use very "simple" queries in order to
> not create an error message - anything a bit "clever" seems to kill it.
> I need to query the database using a date, however, the company that wrote
> the database in the first place stored the date as a string in this format
:
> ccyymmdd (20060424 for example).
> I need to get the current date - less 2 days - and then convert it to that
> format in order to use it in the sql statement. My problem seems to be th
at
> I dont get the ' ' around the value and therefore it never works - throws
a
> very unhelpful error message up (just a long number with a minus sign in
> front of it).
> Can anyone shed any light on this at all for me - I appreciate the above i
s
> a bit vague...
> I've tried using parameters but that didn't work either...
> If I hard code in the date it works fine - ie... WHERE their_date_column
=
> '20060424'
> Any help would be appreciated.
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> Can you use the following code without it 'killing' it?
> Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
Hi Adam,
Many thanks for your reply - unfortunately it would seem not...
The "standard" error message I seem to get whenever I try something that
doesn't work is as follows:
HResult of 0x80040e14 (-2147217900) returned
Unexpected error occurred. An error result was returned without an error
message.
I think it has problems using what I would call "sql server" functions via
the Cache stuff - but I dont know for sure...
My previous idea was to do the "clever" bit - ie the above and slap the
result into another table - then use a
WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
etc...problem then is that I cant use a linked server in the query, I guess
this is something to do with the DTS - when I click on the "black bar" that
joins the object in the designer its obvious that its connecting to the
remote database and therefore I think I'm limited as to what else I can
connect to.
Originally we wanted to do all of this in .net - but we had major problems
connecting to the source - after 3 months we managed it with a dts, and 2
months and we were able to query some tables, 1 more month and we're where
we are now!
I can get ALL data but its like 2 years worth and takes over 30 minutes to
import - the information being "got" is from a Pharmacy in a hospital and we
need the results to appear on a web based report in more of a "real-time"
fashion - ie, not running every 1 hours as I currently have it (sometimes it
takes longer than 30 minutes , some times less)..
Regards
Rob|||OK Rob, I'm clutching at straws now ;-) How about
declare @.sqlstring nvarchar(300)
select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
'''' + convert(char(8),getdate() -2,112) + ''''
exec (@.sqlstring)
This will build the query as
SELECT * FROM UserTable WHERE their_date_column = '20060422'
and then execute it.
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Many thanks for your reply - unfortunately it would seem not...
> The "standard" error message I seem to get whenever I try something that
> doesn't work is as follows:
> HResult of 0x80040e14 (-2147217900) returned
> Unexpected error occurred. An error result was returned without an error
> message.
>
> I think it has problems using what I would call "sql server" functions via
> the Cache stuff - but I dont know for sure...
> My previous idea was to do the "clever" bit - ie the above and slap the
> result into another table - then use a
> WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
> etc...problem then is that I cant use a linked server in the query, I gues
s
> this is something to do with the DTS - when I click on the "black bar" tha
t
> joins the object in the designer its obvious that its connecting to the
> remote database and therefore I think I'm limited as to what else I can
> connect to.
> Originally we wanted to do all of this in .net - but we had major problems
> connecting to the source - after 3 months we managed it with a dts, and 2
> months and we were able to query some tables, 1 more month and we're where
> we are now!
> I can get ALL data but its like 2 years worth and takes over 30 minutes to
> import - the information being "got" is from a Pharmacy in a hospital and
we
> need the results to appear on a web based report in more of a "real-time"
> fashion - ie, not running every 1 hours as I currently have it (sometimes
it
> takes longer than 30 minutes , some times less)..
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
Hi Adam,
Thanks again for the reply.
We tried something like this on Friday - albeit that the date building bit
was different (about 5 times as long :oD) and both that one, and your
example above fail with the same error as earlier :o/
We also tried it without the date creation bit and simply put a hard coded
date in there (in the right format) - but it still didn't work - obviously
doesn't like the parameter stuff...
Any more thoughts? It's getting to the point where I'll be setting up a
reminder in Outlook to go and advance a hard coded date by 1 month each
month :o(
Rob|||No worries Rob ... problem shared and all that :-)
Did you say you were using a DTS package? If this is the case have you
looked at the possibility of using a Global variable? This means you can us
e
a '?' in place of the date and set the ? to a global variable that you can
set up though the 'Package Properties'.
The variable can be checked and changed every time it runs.
If this sounds feasible and you want more info, let me know. If this
doesn't sound like a possibility let me know anyway and I'll see if I can up
my brain a gear! ;-)
Cheers
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Thanks again for the reply.
> We tried something like this on Friday - albeit that the date building bit
> was different (about 5 times as long :oD) and both that one, and your
> example above fail with the same error as earlier :o/
> We also tried it without the date creation bit and simply put a hard coded
> date in there (in the right format) - but it still didn't work - obviously
> doesn't like the parameter stuff...
> Any more thoughts? It's getting to the point where I'll be setting up a
> reminder in Outlook to go and advance a hard coded date by 1 month each
> month :o(
> Rob
>
>|||On Mon, 24 Apr 2006 09:35:16 +0100, Rob Meade wrote:
(snip)
>In leighmans (sp?) I seem to have to use very "simple" queries in order to
>not create an error message - anything a bit "clever" seems to kill it.
Hi Rob,
Can you call user-defined functions? Or execute stored procedures? Both
might be able to work around your problem.
Hugo Kornelis, SQL Server MVP|||"Adam Warne" wrote ...

> No worries Rob ... problem shared and all that :-)
> Did you say you were using a DTS package? If this is the case have you
> looked at the possibility of using a Global variable? This means you can
> use
> a '?' in place of the date and set the ? to a global variable that you can
> set up though the 'Package Properties'.
> The variable can be checked and changed every time it runs.
> If this sounds feasible and you want more info, let me know. If this
> doesn't sound like a possibility let me know anyway and I'll see if I can
> up
> my brain a gear! ;-)
Hi Adam,
Many thanks for the reply.
I dont know whether or not this will work but am more than happy to give it
a try.
Any help would be appreciated - and yes - its via a DTS.
Regards
Rob|||"Hugo Kornelis" wrote ...

> Can you call user-defined functions? Or execute stored procedures? Both
> might be able to work around your problem.
Hi Hugo,
Thanks for the reply.
I dont think so - in the "transformation" part of the DTS I have a SQL Query
box to enter the sql statement - the problems seems to be that because its
source is the remote machine, I cant use linked servers or anything like
that - so unless the tables exist on the remote database I cant use them -
the same is true of SP's...|||"Adam Warne" wrote:

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
> --
> Adam J Warne, MCDBA
Essentially the same thing, but maybe try one of the following variations of
the above solution:
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
CHAR(39) + CONVERT(CHAR(8), GETDATE() -2, 112) + CHAR(39)
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
QUOTENAME( CONVERT(CHAR(8), GETDATE() -2, 112), '''' )
Cheers,
dave