asked on

Convert Date from MM/DD/YYYY to MMDDYY

In SQL server how do you convert the date format of type MM/DD/YYYY to MMDDYY?
Ex ample 07/16/2005 to 71605
Tks
vb

Guy Hengel [angelIII / a3]

select replace(convert(varchar(6), yourdatetimefield, 1), '/', '') from yourtable

Guy Hengel [angelIII / a3]

if the field data type is varchar:

select replace(convert(varchar(6), convert(datetime, yourdatetimefield, 101 ) , 1), '/', '') from yourtable

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbothello

ASKER

DECLARE @Id varchar(10)
set @Id = '03/07/2007'
select
convert(varchar(2),month(@Id))
+convert(char(2),day(@Id))
+right(convert(char(4),Year(@Id)),2)
==================================================
I am looking for a result of ‘30707’ instead of ‘37 07’ ,
basically if the daypart is less that 10, I would like it to me 01,02,03 etc instead of 1,2,3

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

M3MM

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]
GETDATE() : replace it by ur table date attribute
More Formats At ;
http://www.sql-server-helper.com/tips/date-formats.aspx
if ur table date attribute is not datetime for example a varcahar so you need to make type casting to this convertion ...
try it and see ....