Date Format - leading zeros

[sdempsey]

I would like to change a date value I have into a varchar to allow me to join it to a table with a varchar value. I have looked through the forums come up with some ideas none of them seem to work.

I would like yyyymmdd

However at the moment 5th August 2005 is being returned as
200585. I need 20050805.

This is what I have so far:-

convert(varchar (10), year(run_date)) + convert (varchar (2), month([run_date]),101) + convert(varchar (2),day([run_date]), 101)

This is returning the values without the leading 0.

Thanks

 

[DotNetGnat]

how about this:

convert(varchar (10), year(run_date)) + Right('00'+convert (varchar (2), month([run_date]),101),2) + Right('00'+convert(varchar (2),day([run_date]), 101),2)

-DNG

 

[fredericofonseca]

use the correct date format (e.g. YYYYMMDD) and you will reduce the number of string manipulations.

e.g.
replace(convert(varchar(10), run_date, 20),'-','')

will return the expected string.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vongrunt]

How about CONVERT() format 112?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[fredericofonseca]

format 112 only returns yymmdd. Century is ommited and this is a required bit of information.


The only formats that return the information on the correct format (albeit with "-" on it) are
20 (or 120)
21 (or 121)
126

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vongrunt]

112 returns century (yyyy), 12 does not (yy).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[fredericofonseca]

Sorry my mistake.

Misread the books online manual.

as such
convert(varchar(8), run_date, 112)

will indeed be enough.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com