Category: SQL Server

10 May 2016 23:34

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	3

How to change Date Format in sql server 2008 r2

hi All ,

In my sql server the date format is like ss-mm-yyyy.
if i insert like

insert into dates values('11/10/2015','12/10/2015') => this query
it gives output like 11-10-2015.
but i want output how am inserting. my inserted format.
it should be 11/10/2015.
So how to i change the default format.

i want the date format will be the dd/mm/yyyy format Always.
so how can i set it in sqlserver.(bankend)

Thanks with
Paul.S

#765873

11 May 2016 00:34

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	3

very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes.
You could use SET DATEFORMAT statement in SQL
check below snippet
set the format and then INSERT


declare @dates table (orig varchar(50) ,parsed datetime)

SET DATEFORMAT ydm;

insert into @dates
select '2008-09-01','2008-09-01'

SET DATEFORMAT ymd;
insert into @dates
select '2008-09-01','2008-09-01'

select * from @dates

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#765881

11 May 2016 06:00

Joined:	06/10/2010
Level:	Gold
Points:	634

Points:	2

Hi,

SELECT TOP 1 empid,empname,

CONVERT(varchar,joindate,103) 'JoinDate'
FROM Emptest

hope this will help.

#765888

11 May 2016 22:12

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

thanks Sr.Sriram. your code is working fine .

But my requirement is if mi am insert a date in my sql table format like 12/05/2016 . it will inserted and give my inserted date output is like 2016-05-12 . it is set default format is like
yyyy-mm-dd in my sql. So how set it to my our given format. that is in need.

for example
if i insert date on my sql table using sql query.
1.date ='12/05/2016' my needed output : 12/05/2016
2.date ='12-12-2016 my needed output:12-05-2016
3.date='12:05:2016 my needed output :12:05:2016

but now if i am inserted any format of above it give output only like 2016-05-12

So help me to how i am get output to my given format

Thanking you
Paul.S

#765890

11 May 2016 23:06

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	2

Change your dateTime format of Server your SQL date time will be change as per the requirement. basically SQL collect system datetime format and show it like that.

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#765891

11 May 2016 23:20

Joined:	06/10/2010
Level:	Gold
Points:	634

Points:	4

Hi,

SQL Server Date is a binary field (stores binary value regardless of formatting).
So you do not store date in SQL by any format - only displays it in your desired format in your application.

CREATE TABLE #MY_TABLE (TYPE VARCHAR(5),START_DATE DATETIME)

SET DATEFORMAT DMY --Note this setting

INSERT INTO #MY_TABLE (TYPE,START_DATE)
VALUES('DBLMS','01.03.2011');

SELECT TYPE, CONVERT(VARCHAR(10), START_DATE, 105) AS [DD-MM-YYYY]
FROM #MY_TABLE

101 - DD/MM/YYYY
104 - DD.MM.YYYY
105 - DD-MM-YYYY

Hope This will help

#766516

17 Jun 2016 02:27

Joined:	12/12/2013
Level:	Gold
Points:	529

Points:	4

Hi,

Kindly try with the following Query for your Question. It may solve your Problems. while Using dates for inserting into specific format means we have to convert into our needed format Like dd/mm/yyyy or mm-dd-yyyy.

1. Insert into Table_Name(Date_column,Date_Column1)
Values (convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103))
Or
2. Insert into Table_Name(Date_Column,Date_Column1)
Select convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103)
union
convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103)

Regards,
Karunanidhi.K

How to change Date Format in sql server 2008 r2

About dotnetspider.com

Quick Links

General

Services