• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

T-SQL date format

I have a table where I select customer purchase activity and it will display the date of purchase (along with the time) in ColumnA, in this format:
2012-08-06 10:06:42.223
YYYY-MM-DD HH:MM:SS

I want to display the date only, so I added a LEFT function like so:
LEFT(ColumnA,11)

This will get rid of the time but now the date is displayed I will get:
Aug  06 2012


I want to display it in numeric format but as MM/DD/YYYY so it will look like:
08/06/2012
MM/YY/YYYY
Any ideas how I can do this in T-SQL?  Thanks for any assistance.
0
fjkaykr11
Asked:
fjkaykr11
  • 6
  • 5
4 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
-- CASTing as a date will remove the time component, and keep the formatting
SELECT CAST('2012-08-06 10:06:42.223' as date)
0
 
Habib PourfardCommented:
DECLARE   @date DATETIME
SET @date = '2012-08-06 10:06:42.223'

SELECT CONVERT(VARCHAR(10), @date, 101)

Open in new window

0
 
fjkaykr11Author Commented:
@jimhorn.  Thanks for the reply, but i don't want to keep the date the same. It is being displayed as YYYY-MM-DD.  I need it as MM-DD-YYYY.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
fjkaykr11Author Commented:
@pourfard. Thanks for the reply, I am not sure that will work because you to set specific values. This is an ongoing query with many different values that I want to be able to display the date from YYYY-MM-DD to MM-DD-YYYY on the fly.  Any ideas?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>It is being displayed as YYYY-MM-DD.  I need it as MM-DD-YYYY.
That would be the CONVERT function as pourfard commented...

SELECT CONVERT(varchar(10), '2012-08-06 10:06:42.223', 110)

To look up the 110 number and all it's variations, go here and scroll down to the 'Date and Time Styles' table
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
fjkaykr11Author Commented:
@jimhorn. thanks for the link.  Regarding @pourfard post:
I am not sure that will work because you to set specific values. This is an ongoing query with many different values that I want to be able to display the date from YYYY-MM-DD to MM-DD-YYYY on the fly.  Any ideas?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am not sure that will work because you to set specific values.
I had the hard-coded date in there as an example.  Substitute the '012-08-06 10:06:42.223' for whatever you wish, such as...

-- A variable
Declare @dt datetime = '2012-08-06 10:06:42.223'
SELECT CONVERT(varchar(10), @dt,  110)

- A column
SELECT invoice_no, CONVERT(varchar(10), start_date,  110) as start_dt , CONVERT(varchar(10), start_date,  110) as end_dt
FROM invoices

Not real sure what you mean by 'ongoing query with many different values'.  
Please expand on that.
0
 
fjkaykr11Author Commented:
Got the answer from someone else on another forum.
You both were giving me the answer but for a specific value.  The answer I needed was
CONVERT(VARCHAR(10),ColumnA,101) as [Date]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yeah we may have assumed that you'd make the leap from '2012-08-06 10:06:42.223' to ColumnA.  Since we don't have access to your source data, you'll see a lot of experts propose solutions that can be run in anyone's SSMS, and let the asker modify to meet their needs.  That way we can test what we give you in SSMS to make sure it works, before posting it.

Thanks for the split.  Good luck with your project.  -Jim
0
 
fjkaykr11Author Commented:
Jim,  not sure I am following.  What was the SET for? I was thrown off by that.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SET can be used to assign one value to a variable.
The below two lines essentially do the same thing.  

SET @date = '2012-08-06 10:06:42.223'
SELECT @date = '2012-08-06 10:06:42.223'

You can only use SELECT to assign multiple values to multiple variables though...

SELECT @fruit='banana', @number=42

This essentially says the same thing..
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx
0
 
fjkaykr11Author Commented:
ok. thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now