How to update/Change the date format to MM/dd/yyyy from dd/MM/yyyy format in sql server?

Question

In my db DOB is saved in dd/mm/yyyy format. i want to change the DOB date format to MM/dd/yyyy. How can i do that?

Answer 1

First i want to know which datatype you are using for saving your date value. There is nothing provided with your code no sample code, table details nothing. anyway i think your 'date of birth' field datatype is datetime,then you can use the following example

create table checktable(
    ID          int,
    name        nvarchar (30),
    dob  datetime);

Example data insert into the table

insert into checktable(ID,name,dob) values(10,'myname','03/01/2014');

//..........

    select * from checktable

//Use CONVERT() it will give you the desired output

    SELECT TOP 1 ID, dob,CONVERT(varchar,dob,101) 'mm/dd/yyyy' 

 FROM checktable

UPDATE

if your datatype is varchar and now it is in the format mm/dd/yyyy and you want to change it into dd/mm/yyyy format then use the following example it will help you

create table checktable1(
    ID          int,
    name        nvarchar (30),
    dob  varchar(20));

// insert sample data

 insert into checktable1(ID,name,dob) values(10,'myname','21/05/2010');
    select * from checktable1

// change the format using substring()

  select * FROM checktable1
    select dob,substring(dob,4,3)+substring(dob, 1, 3)+substring(dob, 7, 4) from checktable1

It will give you result in 05/21/2010 (mm/dd/yyyy)format