Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Date formatting

I have a date field @Date that currently dislays '07/01/2009'.  What statement can I use in my query to format it it like '7/01/2009'?........m/dd/yyyy?

0
mattkovo
Asked:
mattkovo
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
CONVERT(varchar, datecolumn, 101)
0
 
Randy Wilson.Net DeveloperCommented:
There is no simple T-SQL way to do what you want, the CONVERT will not give you m/dd/yyyy.  
The code below will give you m/d/yyyy.    I have this is a function, because you always seem to need it.  You could edit it to do m/dd/yyyy

CREATE FUNCTION [dbo].[fn_ShortDate]
      (@Date smalldatetime)

RETURNS varchar(12)

AS
BEGIN
DECLARE @String varchar(12)
SET @String = CAST(DATEPART(M,@Date) AS VARCHAR(2)) + '/' + CAST(DATEPART(D,@Date) AS VARCHAR(2)) +
'/' + CAST(DATEPART(YY,@Date) AS CHAR(4))

RETURN(@String)
0
 
Randy Wilson.Net DeveloperCommented:
Oops, need and END at the end of above code
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Anthony PerkinsCommented:
>>What statement can I use in my query to format it it like '7/01/2009'?........m/dd/yyyy?<<
This is a presentation not a data problem.  You should be using .NET for that.  

0
 
NerdsOfTechTechnology ScientistCommented:
use DATE_FORMAT(date,format)

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
SET @Date = DATE_FORMAT(@Date,'%c/%d/%Y')

Open in new window

0
 
Anthony PerkinsCommented:
NerdsOfTech,

I am not sure if you noticed, but the author is using MS SQL Server.
0
 
NerdsOfTechTechnology ScientistCommented:
Oops thanks ac
SET @Date = CONVERT(VARCHAR(10), @DATE, 111) AS [YYYY/MM/DD]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now