July 6, 2008 09:04pm pdt

1. ziolko 43,914
2. developm... 28,529
3. ciuly 27,325
4. TheRealLoki 20,725
5. MerijnB 19,400
6. rfwoolf 17,825
7. SteveBay 10,420
8. Johnjces 8,450
9. kretzschmar 8,302
10. dinilud 5,200
11. RickJ 5,072
12. rllibby 4,272
13. PierreC 3,900
14. mikelitt... 3,347
15. EddieShi... 3,150

1. ziolko 68,621
2. developm... 56,354
3. ciuly 43,325
4. objects 34,974
5. MerijnB 31,946
6. TheRealLoki 29,200
7. CEHJ 27,788
8. rfwoolf 27,725
9. Johnjces 24,625
10. kretzschmar 21,352
11. dinilud 21,060
12. EddieShi... 18,930
13. rllibby 17,462
14. mikelitt... 13,247
15. SteveBay 12,670

05.13.2008 at 04:07AM PDT, ID: 23397263 | Points: 500

	[x] Attachment Details

MS SQL :: Date Time Format

Zones: MS SQL Server, Databases Miscellaneous, Delphi Components

Hi Experts,

Does anyone know what determines the date time field format in a MS SQL database field?
Is it based on your local pc's regional settings or does MS SQL forced down its own format?

Thanks

Start your free trial to view this solution

Question Stats

Zone: Programming

Question Asked By: Marius0188

Question Asked On: 05.13.2008

Participating Experts: 5

Points: 500

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.13.2008 at 04:17AM PDT, ID: 21553826

Rank: Guru

dportas:

There is no format associated with a DATE, TIME or DATETIME column.

Formatting for display purposes is controlled by your client application or presentation tier, not by SQL Server.

The default conversion of strings to dates on the server can be controlled by the SET DATEFORMAT setting but this does not affect display. IMO it's better not to rely on SET DATEFORMAT. Do your own conversions independent of that setting - either on the client or the server.

05.13.2008 at 04:19AM PDT, ID: 21553834

Rank: Sage

matthewspatrick:

Marius0188 said:
>>Does anyone know what determines the date time field format in a MS SQL database field?

SQL Server *does not store* datetime values with any particular format. Rather, the values are
stored as two ints, one encoding the day and the other encoding the time.

When you need datetime output, you should use the CONVERT function to control how the
datetime value is rendered for display, unless your particular need is not provided for in the canned
formatting options.

05.13.2008 at 04:20AM PDT, ID: 21553842

shasunder:

Hi,

Hope the below given link will help you. It describes as
Before you can effectively query date/time (or temporal) data, you have to know something about how date/time values are stored. SQL Server supports two date/time data types: datetime and smalldatetime. The difference between the two is the amount of storage used. Datetime uses 8 bytes of storage, while smalldatetime uses only 4 bytes. For this reason, datetime can represent date/time values within a wider range and with more precision than smalldatetime. These differences are summarized in the table below.
http://www.sql-server-performance.com/articles/dev/date_time_search_p1.aspx

05.13.2008 at 04:24AM PDT, ID: 21553870

Rank: Guru

dportas:

matthewspatrick: "When you need datetime output, you should use the CONVERT function to control how the
datetime value is rendered for display"

Don't do that. To be precise, CONVERT does NOT control how DATETIME values are rendered for display. Only your client app can control that. CONVERT does enable a DATETIME value to be converted to a VARCHAR value in a particular format but I definitely don't recommend doing that whenever you want to display a date. It's an unnecessary overhead. More efficient to return native data types and then control the display in your application code.

05.13.2008 at 04:28AM PDT, ID: 21553899

Rank: Sage

matthewspatrick:

dportas,

You are of course correct, and I was being imprecise. In my haste I was assuming that the Asker
simply needed to control how a datetime was being rendered into text. If that assumption is correct,
then my answer would have been OK as far as it goes, but if a real datetime value were required, then
my post would potentially have been wrong.

Regards,

Patrick

05.13.2008 at 07:37AM PDT, ID: 21555454

Rank: Guru

Zberteoc:

I think that the asker wants to know what determines in SQL server the regional setting and the language. I am not 100% sure but the regional settings are inheritted from the hosting machine, computer, and the language can be set at the SQL server level. It might be possible that at the SQL server installation moment you are asked about the regional settings and the language and you will be provided with the host machine values as defaults. English-US, English-CA etc.

When you right click on a server node in Management Studio or Enterprise Manager and then on Properties you will find under the Advanced for MS and under the Server Settings tab in EM the Language settings that can be changed from English to whatever you can find there.

The regional settings will matter in terms of setting the correct timezone the computer and the servers will run under hence and the correct time as for the language the most of the cases will have effects in regards to dates.

What are the effects of changing the language? I am not sure as I have never done it but I can only asume that if you pick Franch for instance and you want to compare a date column with a string as '13-May-2008' wil fail because in Franch May should be Mai so the correct date should be '13-Mai-2008'.

This doesn't mean at all that the internal date format is different for the French language chosen but only to what values will be correctly interpreted as dates and what dataname values will you get ehn you select something like SELECT datename(dw,getdate()), that is the name of the weekday for today you will lget Mardi for a French settings and Tuesday for English. Also convert functions might have a different result according to the Language settings.

For this reason it is always recomandable to use the so called universal date formats in string values when compared to column dates like '2008-05-13' or '20080513'.

05.13.2008 at 08:11AM PDT, ID: 21555825

Rank: Guru

dportas:

Note that '2008-05-13' is not a "universal" format for date literals in SQL Server 2000 and 2005. It may or may not work depending on the language setting. '20080513' will work whatever the language setting. Use language-independent formats for date literals.

http://www.karaszi.com/SQLServer/info_datetime.asp

05.13.2008 at 09:40AM PDT, ID: 21556811

Rank: Wizard

mark_wills:

Date fields are held as numbers basically inside the database. They are interally "known" to SQL as a datetime construct, so there is no problem or question from that perspective. Date time formats become important when the user or app starts interacting with datetime data.

the SET DATEFORMAT can change the properties when working with data - really just ordnial position of day month and year and makes an assumption that a string being supplied will comply with that format e.g.

set dateformat mdy
select convert(datetime,'13/10/2008') --will fail

set dateformat dmy
select convert(datetime,'13/10/2008') --will succeed

SET LANGUAGE also has an implied date setting - along with code page, but again it is really to help interpret strings. Have a look at:
select * from sys.syslanguages

SQL server will always store it in correct / known datetime construct regardless.

The only real way to work with string variations of date is to use CONVERT which has the added feature of a STYLE code. That style code is an instruction to the convert function as to how to read the string of characters. There are a few of them, and best to refer to Books on-line for a list. The style code must match the string, because it will fail otherwise...

In Delphi, there are a couple of small challenges because Delphi wants everythin in it's own format as well (or used to), so you do have to express the date field correctly formatted with a convert function.

What is the main reason for your question ? Where are you having problems ?

05.13.2008 at 10:42AM PDT, ID: 21557407

Rank: Guru

Zberteoc:

dportas:
<<Note that '2008-05-13' is not a "universal" format for date literals in SQL Server 2000 and 2005. It may or may not work depending on the language setting. '20080513' will work whatever the language setting. Use language-independent formats for date literals.>>

Excellent observation.

I was sure that '2008-05-13' format would be an universal recognizable one regardless of the language settins. I had a lengthy dispute about this with ScottPletcher, he mentioned that in Books online only '20080513' format is said to be universal and no mention for other formats and here is the proof of that:

                       
             --this will work
SET LANGUAGE us_english 
SELECT CAST('2003-02-28' AS datetime)
 
--this will fail
SET LANGUAGE british 
SELECT CAST('2003-02-28' AS datetime) 
 
-- with '20030228' will both work

Open in New Window

20080236-EE-VQP-29 / EE_QW_2_20070628