SQL Load Script Issue

[#1]

Hi,

I have a simple SQL Query which pulls out only two fields from the table updatedb.  The field in particular is called updatetime and it has dates ranging from 03/04/2000 to now.  However from my SQL Script I only to pick up anything greater than 01/01/2008, but when I try and put a where statement in to do just that it ignores the where statement and pulls out all my data.

Attached is a copy of the Query.

The reason I am trying to filter the amount of data from the database, is so that the load script doesnt take ages to run.

Regards,

Jon Ditchfield

[juleshartley]

Can you post just the script - or the qvw with no data, as it's a bit big for downloading!

[#3]

Hi Julian,

The Script is:

SELECT  callref,

              updatetime

FROM    updatedb

WHERE   updatetime > 30/01/2008

ORDER BY callref asc

[SunilChauhan]

check date format of updatetime and use date in single quote

SELECT  callref,

              updatetime

FROM    updatedb

WHERE   updatetime > '30/01/2008'

ORDER BY callref asc;

hope this helps

[#5]

Hi Sunil,

I have tried the single quotes and I get the same issue.  The field appears to be in a Timestamp format.  I have tried putting CAST, TO_DATE, TO_CHAR, TRUNC at the start but still have the same issue.

REgards,

Jon

[SunilChauhan]

the format of date in updatetime and '30/01/2008'.should be same .

other wise

you can convert time stap into date using below  example

CONVERT(VARCHAR(50), updatetime , 121) in sql server

hope this helps

hope this helps

[#7]

I entered the following in my load script:

SELECT  callref,

CONVERT(VARCHAR(50), updatetime , 121)

FROM  updatedb

WHERE   updatetime > 30/01/2008

ORDER BY callref asc

However when reloading, it gives me an ODBC error.

Regards,

Jon

[er_mohit · Accepted solution]

try this

load

callref,

             date( updatetime,'DD/MM/YYYY') as Updatetime

updatedb

WHERE   updatetime > 30/01/2008

ORDER BY callref asc;

SQL  SELECT * FROM   tablename;

[#9]

Thanks for the help, it is exactly what I needed.