# Miscellaneous > General Database Discussions >  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? :Confused:  

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.

----------


## rmiao

Sure.

Table looks like:

col1   col2  col3  tgot                
------ ----- ----- ------------------------
4      a     b     2003-04-08 13:08:27.860

View's code:

CREATE VIEW dbo.VIEW1
AS
SELECT     col1, CONVERT(varchar(10), dbo.Table1.tgot, 20) AS EXPR1
FROM         dbo.Table1
WHERE     (col1 = '4')


Result of select from view:

col1        EXPR1      
----------- ---------- 
4           2003-04-08

----------


## Whack

Coolness.
Found out what the problem was. There are 2 dates in the view, Manual_Promise_Date and Promise_Date. Stupid me only saw the manual date.
Using the code...convert(varchar(10), dbo.[Order].promise_date,110)  AS PromiseDate... I now get the date without the time like 01-03-2003.

What is the easiest way to format the date to appear like 01/03/2003? Can I do this in the view as well? If so how?

Thanks again for the time.
You rule!

----------


## rmiao

Then use

...convert(varchar(10), dbo.[Order].promise_date,101) AS PromiseDate...

----------


## Whack

Thank you very much for all of the help. Trying to learn SQL as quickly as I can but there is soooooooo much.

If it wasn't for this site and people like you rmiao I'd be up the creek without a select statement.

Thanks again!
 :Big Grin:

----------

