How to convert DateTime to Time inside Advanced Query?

How to convert DateTime to Time inside Advanced Query?

I want to run an update command, via advanced query, filtering records wich a DateTime field is between 2 other Time (only Time) parameters.

Does anyone have a solution?
Hi João,
You want to do it inside Advanced Query for some calculation or convert it directly to an output structure?
For SQL Server 2008 and above I think this should work:
CONVERT(TIME, {Entity}.[Attribute])
I want this in a where clause to retrieve records by two time fields.

Something like this:
[Date] BETWEEN @InitialDate AND @FinalDate
[Time] BETWEEN @InitialTime AND @FinalTime
So, in the example below:
Record    Date               Time
1              01/01/2015    10:00
2              01/01/2015    11:00
3              01/01/2015    12:00
4              02/01/2015    10:00
5              02/01/2015    11:00
6              02/01/2015    12:00

When I apply a filter with parameters:
InitialDate = 01/01/2015
FinalDate = 02/01/2015
InitialTime = 10:00
FinalTime = 11:00

Only records 1, 2, 4 and 5 will be retrieved.
Hi João,

The platform uses Date, DateTime and Time although in the database they are all Datetime in SQL Server.
From the OutSystems side you can convert your arguments using the built-in functions (DateTimeToTime, DateTimeToDate, DateToDateTime) meaning you can convert them before they reach the SQL statement and just use what you had:
[Date] BETWEEN @InitialDate AND @FinalDate
[Time] BETWEEN @InitialTime AND @FinalTime

If you want to convert from one entity to your output structure, then use SELECT CONVERT(TIME, {Entity}.[DateTime])
Thank you Tiago.

But I still can't figure out how to compare just the time part of a Date field (of an entity) with a time (only) parameter...
Hi João,

This is getting dirty... :)
It can be really confusing the conversions of dates and times, because there are many possible formats, but making it easy, the cleaner option might work for you:
CONVERT(DATE, {Entity1}.[DateTime])
Between @InitialDate and @EndDate
AND CONVERT(TIME, {Entity1}.[DateTime])
Between CONVERT(TIME, @InitialTime) and CONVERT(TIME, @EndTime)
in which @InitalDate and @EndDate are of "Date" Data Type and
@InitialTime and @EndTime are of "Time" Data Type

If this works, the better. You won't need to get into things like:
cast(CONVERT(varchar(8), {Entity1}.[DateTime], 112) AS datetime)

Hope this helps

The idea seems to be good. But it works only in SQL Server. I forgot to mention that I'm using Oracle.

I have already tried TO_DATE (Oracle function), but it is not compatible...
Ok. Not the same thing... and Oracle is not my thing :)
Anyway I can tell you that for OutSystems the Date, DateTime and Time are of data type Timestamp in Oracle. Try using TO_TIMESTAMP instead
In Oracle I usually use TO_CHAR to return either the time or the date from a date time.

for TIME: SELECT to_char( <<column_name>>, 'HH24:MI:SS' )

for date:  TO_CHAR(<<column_name>>, 'DD-MON-YYYY')

If this doesn't work try the Extract function

Hope this helps!

Yes, TO_CHAR is an option. But I need to compare two time parameters and verify if one is greater than other. Comparing text parameters is not going to accomplish this. Besides, I'll have a performance issue.

I'm going to study Extract option.. Thanks.
It worked fine with EXTRACT in Oracle with something like this:

(EXTRACT(HOUR FROM {Table}.[DateTime]) * 60) + EXTRACT(MINUTE FROM {Table}.[DateTime]) 
    (EXTRACT(HOUR FROM @InitialTime) * 60) + EXTRACT(MINUTE FROM @InitialTime)