| Author |
 Topic  |
|
|
rwaldron
Posting Yak Master
129 Posts |
 Posted - 11/15/2007 : 10:51:39
|
Hi all,
I have a linked server from SQL TO DB2.
I am trying to return data in DB2 where a field MTTRDT is equal to the current date..I know in DB2 I can use WHERE DB2 = CURRENT DATE........However my problem is that my MTTRDT field is not in date format..It is numeric and is yyyymmdd....I have managed to use the SUBSTR command to convert this to a proper date ie yyyymmdd to yyyy-mm-dd.
But I still cannot get this to only return data where MTTRDT (which is now yyyy-mm-dd) = Current Date..
The code below is close, even setting NULL if MTTRDT has a 0 but it falls over saying not vaild data type. Is there any way to get this to work so that I can have a rolling Day query....Thx Ray..
My Curent code is
'SELECT CASE WHEN MTTRDT = 0 THEN null ELSE DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2))) END MTTRDT FROM MVXADTA.ODLINE
WHERE DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2))) >= (current date) ')
|
|
|
georgev
Posting Yak Master
United Kingdom
122 Posts |
Posted - 11/16/2007 : 07:36:40
|
first things first - In SQL Server we use GetDate() to return the current datetime value.
If you have a field with a text value of 20073112 then you can convert this into a datetime value by using
SELECT Convert(datetime, '20073112') As [Look, I'm a datetime value now!]
With this in mind you can now start to compare the two values.
2 methods spring to mind:
[*]Truncate the time value off the date
[*]Compare each datepart separately
Hope this helps :)
George
<3Engaged! |
 |
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 11/16/2007 : 08:33:16
|
i'm not sure this is really relevant? the OP's query is apparently being run against a DB2 database so they need to use the DB2 syntax.
Em |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/16/2007 : 08:41:55
|
but OP stated it uses linked server to DB2. So the query should still be T-SQL
KH
Time is always against us
|
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 11/16/2007 : 08:57:04
|
but that query syntax is not t-sql. They say they're happy with the query except for the current date element, which implies they are running this against the DB2 and returning it to sql server, no?
Em |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/16/2007 : 09:09:21
|
That query is definitely not in T-SQL. If OP is using linked server from MSSQL to DB2 then i am totally confused.
OP better explain clearly.
KH
Time is always against us
|
|
|
| |
Topic |
|
Select Date = Current Date
