SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Select Date = Current Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rwaldron
Posting Yak Master

131 Posts

Posted - 11/15/2007 :  10:51:39  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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!
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 11/16/2007 :  08:33:16  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/16/2007 :  08:41:55  Show Profile  Reply with Quote
but OP stated it uses linked server to DB2. So the query should still be T-SQL


KH
Time is always against us

Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 11/16/2007 :  08:57:04  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/16/2007 :  09:09:21  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000