MSSQL SERVER datetime datatype 2

[lothadio]

How can I change the datetime format, I need to store dates in European format DAY-MONTH-YEAR like 22-02-2002.

The MSSQL SQL Server datetime according to the books online is in this format, Date and time data from January 1, 1753 through December 31, 9999 so this is MONTH-DAY-YEAR

Is there a way to change this?

Thanks

 

[DBCustard]

Try:

select convert(varchar, getdate(), 103)

 

[rajeevnandanmishra]

lothadio,

Use the following command in your application startup.
SET DATEFORMAT DMY

Executing this statement ensure that all the dates are recognized by SQL server in DD/MM/YYYY format.

However, while retrieving datetime data, you should use: select convert(varchar, getdate(), 103)
as stated by DBCustard.

Rajeev

 

[DBCustard]

My recommendation is to format all dates as dd/mmm/yyyy before sending them to SQL server:
e.g.

10/mar/2001 SQL will automatically cast these nomatter what date format is set

 

[tlbroadbent]

Datetime data types are always stored in a numeric format regardless of SQL or OS settings. The dateformat setting only impacts the way date strings are interpreted not the way they are stored. It is very important to underestand the difference between date storage and date display format. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[gilabean]

Sorry guys but I'm still a bit blurr here...

After I use this :

SET DATEFORMAT DMY
"Executing this statement ensure that all the dates are recognized by SQL server in DD/MM/YYYY format."



Is it the date will automatically be stored as dd/mm/yyyy?

 

[SQLBill]

From BOL, datetime data type, overview:

Remarks:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. <part left out> The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

----------
So, as Terry said...dateformat is just used to tell the database how to display the datetime, not how to store it. You apparently can't change how it's stored, just how it's displayed and inputted. SQL Server converts it to or from the built-in format to the format you specify.

-Bill

 

[tlbroadbent]

SQL BOL has this to say about SET DATEFORMAT.
[ul]&quot;This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.&quot;(empahsis added)[/ul]I wrote the following SQL script to demonstrate the use of SET DATEFORMAT and how to display dates in various formats. Hopefully, running this script in Query Analyzer will help clarify the date concepts.

-----------------------------------------------------
-- Create temp table for test
create table #tmp (DateCol datetime)
set nocount on
go

-- Insert a date dmy format
set dateformat dmy
insert #tmp values('30/6/02')
go
-- Insert a date mdy format
set dateformat mdy
insert #tmp values('3/6/02')
go
-- Insert a date ymd format
set dateformat ymd
insert #tmp values('3/6/02')
go
-- Insert a date ydm format
set dateformat ydm
insert #tmp values('3/6/02')
go

--Display dates in default SQL format
Select Datecol As StyleDflt
From #tmp
Go

--Display dates in dd/mm/yy format
Select Style3=Convert(char(8), datecol, 3)
From #tmp
Go

--Display dates in dd/mm/yyyy format
Select Style103=Convert(char(10), datecol, 103)
From #tmp
Go

--Display dates in mmm dd yyyy format
Select Style100=Convert(char(12), datecol, 100)
From #tmp
Go

--Display dates in yyyy/mm/dd format
Select Style111=Convert(char(10), datecol, 111)
From #tmp
Go

--cleanup
Drop table #tmp
go Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[SQLBill]

Lothadio,

I apologize for my incorrect answer...if you haven't tried Terry's script, give it a try. From the way I see it now there's actually three issues (input format, storage format, and retrieval format) with the date and time.

1. SET DATEFORMAT just tells SQL how to interpret the input it is getting. You can change how the date is inputted.

2. SQL stores the date and time in a set format using two 4-byte integers. You can not change how the date is stored.

3. The format specified in the select statement tells SQL how to display the date and time. You can change how the date is displayed.

For Terry,

That's a wonderful script you came up with for explaining this...can you please turn it into a FAQ?

-Bill

 

[tlbroadbent]

I will create a FAQ. Thanks for the suggestion. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com