All Forums Database
pajtasja 4 posts Joined 03/13
14 Jan 2014
SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

Running the following query:

SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime

 Returns the result of:

Maximum(TimeValue) Minimum(TimeValue)
17:59:00.000000    18:00:00.000000

When this table acutally contains 1440 rows (one row for each minute of the day) and I would expect the result to be:

Maximum(TimeValue) Minimum(TimeValue)
23:59:00.000000    00:00:00.000000

When I SELECT * FROM OurDB.DimTime ORDER BY timevalue, I get 1440 rows of data in the wrong sort order from 18:00:00 to 17:59:00.

Row    TimeValue
0001  18:00:00.000000
0002  18:01:00.000000
0003  18:02:00.000000
0004  18:03:00.000000
0005  18:04:00.000000
0358  23:57:00.000000
0359  23:58:00.000000
0360  23:59:00.000000
0361  00:00:00.000000
0362  00:01:00.000000
0363  00:02:00.000000
0364  00:03:00.000000
1437  17:56:00.000000
1438  17:57:00.000000
1439  17:58:00.000000
1440  17:59:00.000000

And when I click on the top of the colum to sort the data, it sorts correctly, as expected.
Can anyone comment on the cause of this behavior?  Has anyone ever seen it before?  I've done a quick google on it and not found an similar answer.

dnoeth 4628 posts Joined 11/04
14 Jan 2014

Your session timezone is set to -6, try

SELECT timeValue AT 0 FROM OurDB.DimTime ORDER BY 1

SQL Data Manipulation Language
Chapter 1: The SELECT Statement
Unexpected Sort Order When Querying DATE Data Type Columns


pajtasja 4 posts Joined 03/13
15 Jan 2014

Thank you!
The AT 0 made it 'right'.  So I'll open up the manual and look for options for setting the session variable and maybe even server-level defaults.
(I knew it was something simple, just didn't know how to fix it.)

pajtasja 4 posts Joined 03/13
16 Jan 2014

Also, to add another note incase anyone comes across this topic:
I was able to find a session level setting which fixed this issue:


We are awaiting our DBAs to address this 'feature' at a server level!

You must sign in to leave a comment.