0

I'm editing a query i build in ms sql. I need to order the items on date, but that gives an error. locations_aanvang gives 2012-08-12(yyyy-mm-dd). so i extend it to 2012-08-12 00:00:00 +1:00 for EST time. So to order the i need to convert it to unix timestamp (right?).

The query is:

SELECT TOP 6 * FROM jd_lighthouses
WHERE locations_aanvang != ''
ORDER BY (SELECT DATEDIFF(s, '1970-01-01', locations_aanvang+' 00:00:00 +1:00')) DESC

And the error i get is: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.

What am i doing wrong? and can i do this easier?

Thanks in advance!

Kind regards, Bram Hammer

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
Bram Hammer
  • 363
  • 4
  • 21

2 Answers2

0

Hmmm. Let me take a stab.

  1. I think MS SQL warns against sorting by a column/calculation that isn't directly in return results of your SQL. This is definitely so in Oracle.
  2. I'm assumming you can plug in a literal value for your variable and it runs just fine?
rs.
  • 26,707
  • 12
  • 68
  • 90
0

Because we can'r clarify to question, only help is that: if locations_aanvang is character type then you can avoid error changing code like this:

DATEDIFF(s, '1970-01-01', locations_aanvang + ' 00:00:00')

Since you use SQL Server Native Client 10.0 i assume you have SQL Server 2008 and DATE datatype. To get newest locations use:

ORDER BY CAST(locations_aanvang as DATE) DESC
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • when i do this i still get the error `Conversion failed when converting date and/or time from character string` – Bram Hammer Jan 24 '13 at 19:38
  • This mean `locations_aanvang` not contain correct date. Which data are you store in `locations_aanvang`? – Hamlet Hakobyan Jan 24 '13 at 19:42
  • aa is see the problem.. some fields are filled with `22-03-2012` instead of `2012-03-22`. What to do now? – Bram Hammer Jan 24 '13 at 19:52
  • 1
    It is because there are thinks named domain integrity. That means date must serves as date. This is tip of the iceberg. What about **strings** like `2012-03-07` is it `7 march` or `3 july`? – Hamlet Hakobyan Jan 24 '13 at 20:11
  • So if i understand your answer there is no way to order them? the string is build as dd-mm-yyyy, so 07-03-2012 means 7 March 2012. – Bram Hammer Jan 24 '13 at 21:56
  • 1
    "some fields are filled with 22-03-2012 instead of 2012-03-22" Howcen they be in format `dd-mm-yyyy`? If all dates in `locations_aanvang` column are in format `dd-mm-yyyy`, then you can use this: `ORDER BY CONVERT(date, locations_aanvang , 105) DESC` – Hamlet Hakobyan Jan 24 '13 at 22:09
  • ok thank you i will take a good look at the code and make sure the dates are saved correct. Again thank you alot for your help! EDIT: emm if i think your last comment is the answer how should i make it that? because i can't like? – Bram Hammer Jan 27 '13 at 22:09
  • I don't understand what is your question? – Hamlet Hakobyan Jan 29 '13 at 09:05
  • I wanted to make your comment the answer but i have to do that with the main comment not an additional. But allready solved it :) – Bram Hammer Feb 05 '13 at 14:59