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.

2 Answers
2

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:

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 JoeFeb 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 JoeFeb 26 '13 at 18:21

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.

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
–
user1363086Feb 27 '13 at 17:23

1

no I think you've missed several important things here...
–
Aaron Bertrand♦Feb 27 '13 at 17:23