How to get time from datetime

[roj2003]

Sorry if this has been asked befor, but I couldn't find anything that hepled me.

I have a datetime field and want to extract only the time. I know it works with CONVERT and style-type 108. But I get something like this: 08:00:00 and I need something like this: 08:00.

Can this somehow be done?

Michael

[MAK]

select left(convert(varchar(10),getdate(),108),5)

[roj2003]

Originally posted by MAK
select left(convert(varchar(10),getdate(),108),5)

Great! Thanks a lot. I have one other problem, maybee you can help me here too:

Depending on what version (German, English etc) of MS SQL Serve you are using it needs other formats when you want to put a date AND time into a datetime column.
Is there a universal format that every MS SQL Server accepts?

Michael

[rmiao]

You can use cast or convert to change style, see 'cast and convert' in books online for details.

[Whack]

I have a similar issue but am new to this so please go easy.
I have a db datetime field. With a stored proc using a view I want to convert the datetime to be just the date, no time returned at all.

I have the date field in the view but an not sure of the syntax.

Please help?

Thanks in advance,
Whack

[rmiao]

Try

select convert(varchar(10),getdate(),110)

[Whack]

here is the date in the table; 2003-02-04 00:00:00.000.
Here is the part of the View that I need the conversion to work in (updated with your suggestion)...convert(varchar(10), dbo.[Order].manual_promise_date,110) AS ManualPromiseDate.
And this is what gets sent back...2003-02-04T00:00:00.0000000-05:00 when I want just 2003-02-04.

Thanks in advance.

[rmiao]

What's data type of manual_promise_date? Tried on datetime column and the code worked.

[Whack]

My view code is as follows...

convert(varchar(10), dbo.[Order].manual_promise_date,110) AS ManualPromiseDate

any ideas or articles. Must be something I'm not getting. I even tried to change the 110 to something else but I keep getting the date in the following format.

<PromiseDate>2003-02-06T00:00:00.0000000-05:00</PromiseDate>

Pulling out what little hair I have left.....

[rmiao]

Look at order table to find out data type of manual_promise_date. Is it datetime or char?

[Whack]

sorry I put the datatype in the subject line on the last post.
It is datetime 8 and allows nulls for what that is worth.

[rmiao]

Try following in query analyzer to see what do you get:

select convert(varchar(10), manual_promise_date,110) AS ManualPromiseDate from order ...

[Whack]

It works in query analyizer but when I put that syntax into the view I get an error near the word Select. I am already doing a distinct select so I dropped the select.
Should I handle the select differently?
I will attach the complete view.

Thanks so much for all the help.

[rmiao]

I just created a view with convert and works again. What's your sql version by the way?

[Whack]

Version of SQL 2000 is 8.00.194.

Can you please post your view syntax.

Thanks again.