Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwaldron
Posting Yak Master
131 Posts |
 Posted - 2007-11-15 : 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
122 Posts |
Posted - 2007-11-16 : 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[CODE]SELECT Convert(datetime, '20073112') As [Look, I'm a datetime value now!][/CODE]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
1208 Posts |
Posted - 2007-11-16 : 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)
17689 Posts |
Posted - 2007-11-16 : 08:41:55
|
but OP stated it uses linked server to DB2. So the query should still be T-SQL
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-16 : 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)
17689 Posts |
Posted - 2007-11-16 : 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[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Select Date = Current Date