only keep date for time stamp in script

[yelin_nyu]

Hi,

I know in expression, you can use date(), but what about in script? Is there an easy way to only keep the date, because it's giving me problems when i manipulate data from script (duplicate dates because hours and mins don't match)

Thanks

[swuehl]

Use daystart() or floor() to get rid of the time information (I assume your timestamps are correctly interpreted as such by QV):

Date(Floor(TIMESTAMP)) as DATE

[yelin_nyu]

date() function won't work in script because date' is not a recognized built-in function name. Also, I tried daystart(), I didn't work either

[MayilVahanan]

Hi

i think try with date#() function..may be format issuse..

[yelin_nyu]

no, date#' is not a recognized built-in function name. Sorry guys, this is in script, not on front end. it's part of sql code (sql select)

[swuehl]

Ah, I missed that part of your problem.

Use a preceding LOAD statement:

LOAD

Date(floor(TIMESTAMP)) as DATE;

SQL SELECT ...

[yelin_nyu]

this is what i used:

Order:

Load *

case when source='f' and rcvd_date>load_date-3

then Date(floor(load_date)+7)

else Date(floor(load_date))

end as LD

resident tempOrder;

error msg missing FROM statement (I know it's this part becuz when i commented out everything, and try load a little by little, the code worked until I uncommented the above case statement.

[MayilVahanan]

Hi

try like this

load *, if(source = 'F' and rcd_date>loaddate-3,date(floor(load_date)+7),date(load_date)) as ld resident tablename;

Hope it helps