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?
What is the data type of each column (date or character)?
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.
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.