Date Conversion

Question

Post reply

Date Conversion

Cory Slaney

SSCrazy

Points: 2459
More actions
May 19, 2005 at 1:20 pm

#67702

I am getting date returned from various tables in mm/dd/yyyy format but some of the values in the table are 2/3/2005, 5/13/2005. I need the leading zeros returned before the month and day. Is there a way to force this to happen for single digit day and month values? Any help would be greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Is the date kept in a date column a varchar column?

If varchar :

Select convert(datetime, yourcolumn) as DateCol from dbo.YourTable.

Cory Slaney SSCrazy Points: 2459 More actions · Answer 2

It is a varchar and your example above did not seem to work. The error returned was :

"Syntax error converting datetime from character string."

and the code I used was:

set @newdate = convert(datetime,@mdate)

The value of @mdate that I am testing on returns 5/2/2005 and I want it returned as 05/02/2005.

Any other suggestions?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This means that the data is incorrectly formated in the column.

Can you run this query and send me the results?

Select * from dbo.YourTable where IsDate(VarcharDateCol) = 0

BTW, those are the rows that are causing you trouble when running the convert.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 4

Since you are using vchar data to store your dates you might want to consider one of these methods of formating your data

HTH Mike

DECLARE @vDT VarChar(200)

SET @vDT = '5/12/2005'

SELECT convert(DateTime,@vDT) AS 'YYYY/MM/DD',

Convert(Char(11),cast(@vDT as DateTime)) AS '3 letter Abbreviation'

--Returns

--2005-05-12 00:00:00.000 May 12 2005

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Cory...

This works the way you want... leading zeros and mm/dd/yyyy format intact...

DECLARE @Date VARCHAR(30)

SET @Date = '2/3/2005'

SELECT CONVERT(VARCHAR(10),CONVERT(DATETIME,@Date),101)

----------

02/03/2005

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 6

This is a cool date format function I found a while back. See header comments for credits...

/*

//////////////////////////////////////////////////////////////////////////////////

Author: Rusty Hansen 8-21-2001

Description: Formats a date to a specific format.

Parameters:

@dDate = A value or field of datatype datetime or a value or field that can be explicitly converted to

a datetime datatype.

@sFormat varchar(40) = Format codes using the characters described below

MMMM or DDDD = the full name for the day or month

MMM or DDD = the first 3 letters of the month or day

MM or DD = the two digit code signifying the month or day

M1 or D1 = the month or day value without a preceding zero

YYYY = a four digit year

YY = a two digit year

All other characters will not be replaced such as / - . * # a b z x % and will show

up in the date in the same relative position that they appear in the format

parameter.

Examples

select dbo.FormatDate('9/21/2001','dddd, mmmm d1, yyyy') --> Friday, September 21, 2001

select dbo.FormatDate('9/21/2001','mm/dd/yyyy') --> 09/21/2001

select dbo.FormatDate('9/21/2001','mm-dd-yyyy') --> 09/21/2001

select dbo.FormatDate('9/21/2001','yyyymmdd') --> 20010921

select dbo.FormatDate('9/5/2001','m1/d1/yy') --> 9/5/01

select dbo.FormatDate('9/21/2001','mmm-yyyy') --> Sep-2001

//////////////////////////////////////////////////////////////////////////////////

*/

create function [dbo].[fnFormatDate]

(

@dDate datetime --Date value to be formatted

,@sFormat varchar(40) --Format for date value

)

returns varchar(40)

as

begin

-- Insert the Month

-- ~~~~~~~~~~~~~~~~

set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))

set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))

set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))

set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2))))

-- Insert the Day

-- ~~~~~~~~~~~~~~

set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))

set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))

set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))

set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2))))

-- Insert the Year

-- ~~~~~~~~~~~~~~~

set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))

set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12))

-- Return the function's value

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~

return @sFormat

end