• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • 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
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!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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