Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Can you post just the script - or the qvw with no data, as it's a bit big for downloading!
Hi Julian,
The Script is:
SELECT callref,
updatetime
FROM updatedb
WHERE updatetime > 30/01/2008
ORDER BY callref asc
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
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
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
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
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;
Thanks for the help, it is exactly what I needed.