CODEWITHSUNDEEP
sqlsql-server
compcobalt
compcobalt

Reputation: 1362

MS SQL order by date, time(AM/PM)

I'm trying to order by date and time but the time is in am/pm format so I tried to convert it to military time but it still doesn't work correctly. CRT_DATE is data type: date and CRTUP_TIME is nvarchar

MyQuery = "SELECT * FROM TABLE1 WHERE ACTIVE=1 and ORIGINAL_P=2 ORDER BY CRT_DATE, CONVERT(VARCHAR(8), CRTUP_TIME, 108)"

Can someone please explain to me what I'm doing wrong ? Thanks so much for any help!! :)

Upvotes: 0

Views: 630

Answers (2)

Leng
Leng

Reputation: 51

I'm not sure you need to convert CRTUP_TIME. However, if CRTUP_TIME contains actual time like "10:05:44:00 AM", your conversion limited to only the first 8 characters resulting "10:05:44" and truncating the rest.

Upvotes: 0

Andrew Loree
Andrew Loree

Reputation: 264

Your CONVERT() doing essentially nothing in terms of type conversion. Your converting an nvarchar to a varchar. You need to convert it to a TIME data type, to sort accordingly. Try instead:

CONVERT(TIME,CRTUP_TIME)

Upvotes: 1

Related Questions