SUBSTRING Function

See the Uses of SUBSTRING, CHARINDEX, REPLACE Functions in MSSQL.


SELECT
'3/6/2010' as OLD_FORMAT,
charindex('/','3/6/2010',1) [FIRST/POSITION],
charindex('/','3/6/2010',3) [2nd/POSITION],
replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','') as MM,
replace(left('3/6/2010',2),'/','') as DD,
right('3/6/2010',4) as YYYY,

replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','')+'/'+
replace(left('3/6/2010',2),'/','')+'/'+
right('3/6/2010',4) as NEW_FORMAT

Example to convert date  dd/mm/yyyy to mm/dd/yyyy

Advertisements