Change date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change date format

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-05 : 08:52:46

When I insert dates into a datetime column on my server its using the US format, i.e. today is 12/05/2008

How can I make it use the UK format 05/12/2008

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-05 : 09:07:46

select convert(varchar,getdate(),103)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-05 : 09:11:53

I'd say, use the above when reporting. Otherwise, Keep the format as it is in the column. Converting a columns from datetime to varchar isn't a good practice.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-05 : 09:53:25

Is the conversion happening at database or on application?
The language settings in SQL Server affect date formats.
Check the default language, and language the login the application is using to connect to Database:
To change default date settings:
set language 'british english'
To change individual logins:
alter LOGIN [server\login] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[british]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 11:58:12

It shouldn't matter assuming that your front end is pulling the values (AS DATETIMES) and putting them into a suitable container.

There are 2 ISO standards that are not effected by the locale setting.

They are

Short Form : (YYYYMMDD)
Long Form : (YYYY-MM-DDTHH:MM:SS.MS)

you can use them and they will be the same regardless of the locale setting.

example


SELECT
	CONVERT(CHAR(11), CAST('2008-02-01' AS DATETIME), 106) -- Can Change
	, CONVERT(CHAR(11), CAST('20080201' AS DATETIME), 106) -- Consistent
	, CONVERT(CHAR(11), CAST('2008-02-01T12:00:00' AS DATETIME), 106) + ' ' + CONVERT(CHAR(11), CAST('2008-02-01T12:00:00' AS DATETIME), 108) -- Consistent
	, CONVERT(CHAR(11), CAST('2008-02-01 12:00:00' AS DATETIME), 106) + ' ' + CONVERT(CHAR(11), CAST('2008-02-01 12:00:00' AS DATETIME), 108) -- Can Change

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:08:47

quote:
Originally posted by sakets_2000

I'd say, use the above when reporting. Otherwise, Keep the format as it is in the column. Converting a columns from datetime to varchar isn't a good practice.

even for reporting purpose, there's no need to do this in SQL. you can very well get date in format you want in front end using formatting functions available (most reporting tools have that)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources