Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

Question

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?

Answer 1

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

Answer 2

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