Change date, month in datetime datatype column

Suppose you want to change the date, month in datetime datatype column

Ex: 1966-09-01 00:00:00 ---->>>>  1966-01-09 00:00:00

Table Name:  TableName
Column Name: dob

update TableName set  dob=convert(datetime, convert(varchar(10), convert(varchar(4),year(dob))+'-'+right('0' +convert(varchar(2),day(dob)),2)+'-'+ right('0'+convert (varchar(2),month(dob)),2)),101)



Created by Vikas Jaiswal
Contact on vikasjaiswal.ctit@gmail.com