CODEWITHSUNDEEP
sqlsql-serversql-updatedate-formatting
Deepak
Deepak

Reputation: 97

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

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?

Upvotes: 0

Views: 22348

Answers (5)

Mahek Shah
Mahek Shah

Reputation: 1

This is the query to show all data in dd/mm/yyyy format.Type your column name that contains date in place of datecolumnname and your table name in place of tablename in below query:

 select convert(varchar,datecolumname,103) as datecolumname from tablename

Upvotes: 0

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

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

Upvotes: 2

I A Khan
I A Khan

Reputation: 8859

if your data type is DateTime then no need to convert it, in later you can convert its format as you want but if your are using varchar or nvarchar then you can use CONVERT() as below

SELECT convert(datetime, '23/10/2016', 103)

Result : 2016-10-23 00:00:00.000

if just want to conver in date then

SELECT convert(date, '23/10/2016', 103)

Result : 2016-10-23

for more information and other formats

http://www.sqlusa.com/bestpractices/datetimeconversion/

and if you just want to convert string to sting then

SELECT CONVERT(varchar,'23/10/2016',101)

Result : 23/10/2016

Upvotes: 0

Erik Ušaj
Erik Ušaj

Reputation: 159

Microsoft SQL: https://msdn.microsoft.com/en-us/library/ms187928.aspx

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

use example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

For other databases search Date and Time Functions in documentation.

Upvotes: 1

Ben Echols
Ben Echols

Reputation: 1

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

http://www.w3schools.com/sql/func_convert.asp

Upvotes: 0

Related Questions