CODEWITHSUNDEEP
sqlsql-server
Jeremy
Jeremy

Reputation: 121

Return date only (not time)

I have a column that stores date information as a varchar(8). All of the data is entered as yyyymmdd. I would like to cast this information in a view to display as a date. When I try:

SELECT CAST(HIRE_DATE AS datetime)

The values are returned with the time. For example a record that has 19951107 casts as 1995-11-07 00:00:00.000.

How can I have it just return "1995-11-07" without the time value?

Upvotes: 0

Views: 930

Answers (2)

Dave Mason
Dave Mason

Reputation: 4936

This would seem to be what you're looking for:

SELECT CONVERT(VARCHAR, CAST(HIRE_DATE AS DATETIME), 102)

Note that the return value is VARCHAR, not a true DATE, DATETIME, etc.

If you need it as a DATETIME datatype then omit the CONVERT.

SELECT CAST(HIRE_DATE AS DATETIME)

CAST and CONVERT should be helpful.

Upvotes: 1

Greenstone Walker
Greenstone Walker

Reputation: 1150

The answer from @DMason (cast(HIRE_DATE as datetime)) is the correct one if you have SQL Server 2005 or earlier.

SQL Server 2008 introduced the date datatype which means you can achieve what you want with cast(HIRE_DATE AS date).

If there are invalid dates in your varchar(8) column then cast() will cause the query the fail. SQL Server 2012 introduced the try_ conversion functions which return a null marker if the cast is impossible. So, you can achieve what you want with try_cast(HIRE_DATE AS date).

From Books Online: Conversion functions.

Upvotes: 0

Related Questions