CODEWITHSUNDEEP
sqlsql-server-2005
Nixphoe
Nixphoe

Reputation: 250

SQL Date Conversion

I'm looking to convert a date formate from yyyymmdd to mm dd yyyy

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'

Upvotes: 1

Views: 239

Answers (4)

AndyD273
AndyD273

Reputation: 7279

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.

Upvotes: 1

tofutim
tofutim

Reputation: 23374

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

Upvotes: 3

Jerad Rose
Jerad Rose

Reputation: 15513

This will work:

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 3

Related Questions