Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.

I have a web based form with multiple fields including a field that is hidden on the form that writes the CURRENT_TIMESTAMP to the database when the form is submitted.

The challenge I'm facing is that I want to create reports from these forms (sorting by month, week, or year) and order them by the timestamp but based on what I've seen here, sorting is not possible.

Is there an alternative I can use to achieve the results I want?

share|improve this question
1  
@PaulWhite or OVER (ANY_ABRITRARY_ORDER_YOU_LIKE) –  Aaron Bertrand Feb 26 '13 at 21:34
    
@PaulWhite - Basically I want to pull all records that were entered into the database (for example) during a given month based on the CURRENT_TIMESTAMP. –  Techie Joe Feb 26 '13 at 22:27
    
Is it a given month or is it the current month? –  Aaron Bertrand Feb 27 '13 at 2:39
    
@AaronBertrand They're basically month end reports for the management types broken down by Month, YTD, and things like that. –  Techie Joe Feb 27 '13 at 16:34
    
Doesn't answer my question. Are you always basing the date range on CURRENT_TIMESTAMP or are you sometimes expecting to dictate a date range? –  Aaron Bertrand Feb 27 '13 at 16:35

2 Answers 2

up vote 5 down vote accepted

I have no idea why you would want to ORDER BY CURRENT_TIMESTAMP. If the table has a column where you stored CURRENT_TIMESTAMP when the row was created, you just need to say:

ORDER BY that_column_name;

Or if you want newest first:

ORDER BY that_column_name DESC;

... in other words, forget how the column was populated.

Now, if the comments are accurate, and you're actually not concerned so much about ordering but rather about filtering, then perhaps what you want is:

-- create a DATE variable based on today:

DECLARE @s DATE = CURRENT_TIMESTAMP;

-- convert it to the first of the month
-- (you can do this in the step above,
-- just separating it for clarity):

SET @s = DATEADD(DAY, 1-DAY(@s), @s);

-- use >= and < in a WHERE clause, and order by that column
-- (however this is just a coincidence, order by and filtering
-- are completely unrelated in this case:

SELECT [?] FROM dbo.[?]
  WHERE [datetime_column] >= @s
  AND [datetime_column] < DATEADD(MONTH, 1, @s)
  ORDER BY [datetime_column];

Here is why you don't want to use BETWEEN, in case anyone sends you that way:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

Here is why you want to use built-in date functions and a native DATE type and not convert to a string to trim time from a datetime variable:

http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime

http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time

And here is some general information about bad practices in date/range queries:

\http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

share|improve this answer
    
When I add complexity to a statement such as SELECT * FROM table_name WHERE current_timestamp_column >= 'Jan 01 2013 10:00AM' order by current_timestamp_column I only get partial results. If I'm going to sort by Month, for example, out of 30 records that would qualify, only 20 show up. –  Techie Joe Feb 26 '13 at 18:18
1  
Well why are you saying Jan 01 2013 10:00AM instead of 2013-01-01T10:00? Why do you think this problem has anything to do with ORDER BY? –  Aaron Bertrand Feb 26 '13 at 18:20
    
I was unaware of this. Never been in this situation with this scenaro before. Is there a link that I could look over to learn up on it? –  Techie Joe Feb 26 '13 at 18:21
2  
2  
More importantly, ORDER BY will never change the number of rows returned, it can only change the order. –  Aaron Bertrand Feb 26 '13 at 22:23

Yes you can do that using datepart function. create view and add the 3 more column as datepart(mm,CURRENT_TIMESTAMP) month, datepart(wk,CURRENT_TIMESTAMP) week datepart(yy,CURRENT_TIMESTAMP) year and apply the sort operation on view.

for more information you can refer to Datepart Function

share|improve this answer
    
So confused. How is creating three contrived columns and ordering by them individually better than ordering by the column itself? Also if you really mean ORDER BY CURRENT_TIMESTAMP then what does the order by mean, really? Isn't it the same value on every row? –  Aaron Bertrand Feb 26 '13 at 21:17
    
hi as per your question you want to sort by month, week, year –  user1363086 Feb 27 '13 at 17:23
1  
no I think you've missed several important things here... –  Aaron Bertrand Feb 27 '13 at 17:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.