Date & time Convert

[esanray]

my model contain sch date in a column & sch time in another column
sch date         sch time
18-NOV-11       0620
18-NOV-11       1705
18-NOV-11       2330
18-NOV-11       1500


i want to filter this according to my system time that means when i run the report at 1500 then it will display +2 hours

i thought i concatenate the two Colum then convert it in to date & time format then i filter it from the system time, but i am unable to convert it in to date & time format.

can anybody help how to write the query?

[tjohnson3050]

What is the data type of each column (date or character)?

[HalfBloodPrince]

Try this.
Make ur time in proper format then try this Function

to_date(cast([Date]+' '+'[Calculated Time]',varchar(15)),'yyyy/mm/dd hh:mi:ss PM')

considerd that ur date column is in character format else u have to cast it too als check the date Format of urs its dd-mm-yy.
so make the changes accordingly.

[the6campbells]

unclear from your explanation what if the physical types of those columns are DATE and TIME.

if they were then you would filter based on current_date and local_time

if you want to increment the time then you can define local_time + interval 2 hours

if the columns are characters then you need to clarify if you have stored them as character representations of ISO-DATE and TIME literals YYYY-MM-DD and HH:MI:SS.nnn, numbers or strings. If they aren't then you can consider using cast( extract ( year, date), char(4)) || .... and so on.