MySQL Query to MS SQL

Question

I have the following MySQL Query:

SELECT date, time, custom_id, Number
FROM traffic t
WHERE CONCAT(date, " ", time) BETWEEN "01/06/2014 00:00" AND "01/10/2014 23:00"
AND Number = 
(SELECT MAX(CAST(Number as SIGNED)) FROM traffic WHERE t.date = date and t.custom_id = custom_id)
ORDER BY date, CAST(Number as SIGNED) DESC, custom_id

This is the SQLFiddle in MySQL (working): http://sqlfiddle.com/#!2/6e1248/3

How can I convert/translate it to MS SQL? SQLFiddle (not working): http://sqlfiddle.com/#!6/6e124/1

Thanks in advance

why are date and time separate columns? And unless date is in a proper DATE format, this won't work in MySQL either! — Strawberry
– Strawberry, Commented Feb 11, 2014 at 14:29
The query presumably doesn't work in MySQL because of the date time formats. The between will not work when it crosses month boundaries. — Gordon Linoff
– Gordon Linoff, Commented Feb 11, 2014 at 14:30

bdn02 · Accepted Answer · 2014-02-11 14:29:29Z

2

SELECT date, time, custom_id, Number
FROM traffic t
WHERE CONCAT(date, ' ', time) BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
AND Number = 
(SELECT MAX(CAST(Number as smallint)) FROM traffic WHERE t.date = date and t.custom_id = custom_id)
ORDER BY date, CAST(Number as smallint) DESC, custom_id

answered Feb 11, 2014 at 14:29

bdn02

1,5109 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3288852 Over a year ago

Worked perfectly. Exact same result, Thanks!

bdn02 Over a year ago

Thanks to you, with the link to SQLFiddle is all more simple!

Jim · Accepted Answer · 2014-02-11 14:29:05Z

1

The main issue is that SQL server doesn't like " to enclose strings, these will get treated as a column name removing these (and the CASTs) works:

SELECT date, time, custom_id, Number
FROM traffic t
WHERE CONCAT(date, ' ', time) BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
AND Number = 
(SELECT MAX(Number) FROM traffic WHERE t.date = date and t.custom_id = custom_id)
ORDER BY date, Number DESC, custom_id

Edit: Whoops, just noticed cast is needed. See the answer by @bdn02 for an example.

answered Feb 11, 2014 at 14:29

Jim

22.7k6 gold badges55 silver badges81 bronze badges

1 Comment

user3288852 Over a year ago

That's why I wasn't getting the result I was expecting. Thanks!

Collectives™ on Stack Overflow

MySQL Query to MS SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related