SQL Date Conversion

I'm looking to convert a date formate from yyyymmdd to mm dd yy开发者_如何学Goyy

I found quite a few resources of SQL statements. But I'm too new to really know what I'm looking for.

I have the following expression and I need it to shoot out the date

select Date_of_hire from Employee where employeeID='input'

Choosing the date format is a job best done in the presentation layer (your forms app, your ASP app, your report etc). Let SQL simply return the DATETIME field and present it in whatever format you fancy by properly setting the output your app.

SELECT REPLACE(CONVERT(VARCHAR(10), requested, 110), '-', ' ') AS hiredate

Take a look at http://www.sql-server-helper.com/tips/date-formats.aspx for further reference. Note that this is equivalent to

SELECT REPLACE(CONVERT(VARCHAR(10), requested, 101), '/', ' ') AS hiredate

I've run into this a few times importing data from a AS400 (date stored as string) into a sql table where there isn't any application in the middle to do the formatting, so I can see why you might want to do this.
Look into Cast and Convert() ( http://msdn.microsoft.com/en-us/library/ms187928.aspx )

select Convert(varchar,Cast(Date_of_hire as datetime) ,101) as Date_of_hire from Employee where employeeID='input'

This will format it like 01/01/2001.
The problem with convert is that it is really limited on how it formats it.
To get a date from a string like that, you may be able to leave convert out and just Cast as datetime.

This will work:

select replace(convert(varchar, Date_of_hire, 101), '/', ' ') 
from Employee where employeeID='input'