CODEWITHSUNDEEP
sql-serverdatecasting
user3075320
user3075320

Reputation: 3

SQL, How do I trim date to exclude the time?

for my query, I have to pull the dates from a table but they include the time as well. How do I trim in to not include the time?

CAST(m.START_DATE AS varchar(12)) AS StartDate

but what happens is, my result shows up as "Jun 12 2013". How would I change it to show up as 6/12/2013?

Upvotes: 0

Views: 4236

Answers (4)

Hart CO
Hart CO

Reputation: 34784

You can lob off the time from a DATETIME field by using the DATE datatype in SQL Server:

SELECT CAST(m.START_DATE AS DATE) AS StartDate

For formatting, you can use CONVERT() and find a code that fits the format type you're after, but if you're using SQL Server 2012 you can use the handy FORMAT() function:

SELECT FORMAT(m.START_DATE,'MM/dd/yyyy')

It is worth noting that FORMAT() is slow, I wouldn't use it for anything other than formatting output, which for the most part is best done outside of SQL (presentation layer).

Upvotes: 1

LittleSweetSeas
LittleSweetSeas

Reputation: 7074

You can use the cast-to-date function to strip out time parts:

SELECT CAST(m.START_DATE AS DATE)

You may found useful to read also about CONVERT function:

MSDN Link

Eg:

SELECT CONVERT(varchar(10),m.START_DATE,102)

This would help you to format date in your favourite style.

Upvotes: 3

Pandian
Pandian

Reputation: 9126

Try like below.. It will help you...

IN SQL SERVER -> SELECT CONVERT(varchar(10),datestring,101)

IN MYSQL -> SELECT STR_TO_DATE(datestring, '%M/%d/%Y')

IN Oracle -> SELECT to_char(datestring,'MM/DD/YYYY')

Upvotes: 0

Sam
Sam

Reputation: 2761

You can try the DATE() function So it would be 

SELECT DATE(m.START_DATE)

This works for MySQL

For SQL Server try:

SELECT CONVERT(date, m.START_DATE)

Upvotes: 2

Related Questions