I have a Table where i have 2 fields - date_col and time_col.
DATE_COL - DATE FORMAT 'YY/MM/DD'
TIME_COL - TIME(6)
I am trying to select the min(time_col) for a particular date / current_date...
When i select all the records from the table for a particular date, i can see the min of time_col is '00:01:33'
But when i run a query saying
SELECT MIN(TIME_COL) FROM TABLE_NAME WHERE DATE_COL = 'current_date'
My results is showing me '17:00:07'
I tried converting the time to different formats and adding it to date field so i can check it as one value, but my result always comes back with '17:00:07' as the min rather '00:01:33'
Please help me understand what am i doing wrong here.. Please help me with the correct query to get me the exact min time for a particular date.. I am using this min time to process all the records for this particular date and join with another source table to fetch the records.. Unfortunately this is a source table where i am not able to change the structure to have one column (TIMESTAMP(6))
I am not sure if this is a ODBC connection problem or SQLA showing the results as this way...
any help would be greatly appriciated
Hi, Try the following:
SELECT MIN(TIME_COL) OVER(Partition by Date_Col Order By Time_Col)
Here are some sample data...
I even tried to run this but no luck... its always giving me 17:00:07
SELECT MIN(act_tm) FROM TABLENAME
WHERE act_dt IN (
SELECT MIN(act_dt) FROM TABLENAME
WHERE act_dt BETWEEN'2013-06-01' AND '2013-08-01'
Do an ORDER BY act_tm and you'll see what happened :-)
TIME is internally stored as UTC and sorted based on this, MIN returns the minimum value based on this order and your session timezone is probably set to -7.
SELECT min(cast(act_tm as char(8))) should return '00:01:33'.
SQL Data Manipulation Language
Chapter 1: The SELECT Statement
ORDER BY Clause
Unexpected Sort Order When Querying DATE Data Type Columns