Date format changing - 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 2000 Forums

Transact-SQL (2000)

Date format changing

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2006-05-16 : 09:10:46

I am adding a date to a database using an INSERT Statement. The date is in British format (dd/mm/yyyy) going into the stored procedure but it gets converted to American format (mm/dd/yyyy) when it is added to the datbase.
Anyone know how I can prevent this happening as I want it stored as British Format?

Thanks,

macca

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-16 : 09:49:34

Dates are not stored in datetime columns in a readable format.

The best way to insert the data is to use format YYYYMMDD, because it is unambiguous for SQL Server to convert it correctly to the internal format.

Read the "SET DATEFORMAT" and "CAST and CONVERT" subjects in the Transact-SQL section of SQL Server Books Online for more background on date fromats.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 07:16:52

When you insert date, make sure you send it to SQL Server in universal format yyyymmdd or yyyy-mm-dd so that your date will be stored correctly regardless of the Local datesetting. If you use Front end application to send data then format the date before sending it to database table

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2006-05-17 : 08:23:03

Thanks for reply Madhivnan.

You wouldn't have some code that does something like that?

The date format I want is dd/mm/yyyy but am getting changed to mm/dd/yyyy.

macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 04:13:25

As I said, If you use Front end application then format the date to yyyymmdd format before using it in insert statement

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 04:37:19

"You wouldn't have some code that does something like that"

if you are sure that the client application will present the data in UK format then use CONVERT to change it to datetime datatype, or use SET DATEFORMAT DMY as MVJ suggested, for example:


DECLARE	@MyDate		datetime,
	@MyStringYY	varchar(50),
	@MyStringYYYY	varchar(50)
SELECT	@MyDate = '20030201',
	@MyStringYY = '01/02/03',
	@MyStringYYYY = '01/02/2003'
SELECT	[Native] = @MyDate,
	[Type 3] = CONVERT(varchar(50), @MyDate, 3),
	[Type 103] = CONVERT(varchar(50), @MyDate, 103)

SELECT	[MyString to 3] = CONVERT(datetime, @MyStringYY, 3),
	[MyString to 103] = CONVERT(datetime, @MyStringYYYY, 103)

SET DATEFORMAT dmy	-- Indicate that date conversions should assume D...M...Y style
SELECT	[DATEFORMAT YY] = CONVERT(datetime, @MyStringYY),
	[DATEFORMAT YYYY] = CONVERT(datetime, @MyStringYYYY)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources