Select time from datetime in SQL Compact

Question

Much like this question: "How to get Time from DateTime format in SQL?", I am trying to select only the time from a datetime, but unlike the above question, I would like to know how to do it in SQL Server Compact. Does anyone know how to do this?

Luigi Saggese · Answer 1 · 2013-04-02T09:54:40.417

1

SELECT DATEPART(hour, OrderDate), DATEPART(minute, OrderDate) FROM MyOrders

Ref. http://msdn.microsoft.com//library/ms173998%28v=sql.90%29.aspx

edited Apr 02 '13 at 09:54

answered Apr 02 '13 at 09:39

Luigi Saggese

5,299
3
43
94

This isn't working quite how I want. When I use the query `select top(1) DATEPART(hour, DateAdded) + DATEPART(minute, DateAdded), DateAdded from table`, the top result is `38, 02/04/2013 10:28:47`. Not sure how the 38 relates to that date? – Mike Baxter Apr 02 '13 at 09:45
Ah, just worked it out. It has literally added the two numbers together, instead of concatenating them. – Mike Baxter Apr 02 '13 at 09:49
1

Edited. There was an error because + operator sum 2 return datepart (return an integer) – Luigi Saggese Apr 02 '13 at 09:54

score 1 · Accepted Answer · answered Apr 02 '13 at 09:55

Solved it. Luigi's answer is not actually the correct one but I have upvoted it, as it helped me find the answer.

To get only the time from a datetime in SQL Server Compact, the proper query is:

select ltrim(str(DATEPART(hour, columnName))) + ':' + ltrim(str(DATEPART(minute, columnName))) + ':' + ltrim(str(DATEPART(second, columnName))) from table

Select time from datetime in SQL Compact

2 Answers2

Linked