How to modify date format

Question

0.00/5 (No votes)

See more:

So I was just wondering, when creating a table is there a way to make sql server only accept certain date formats.

I've created the table now, and it would be annoying to create it all again as the relationships are already in place, but could i alter the table to accept the british format of the date as im british.

So something like

ALTER Employee
Set Date to format (DD-MM-YYYY)?

Thanks in advance, its probably something simply..
It just saves me reading it like MM-DD-YYYY, which will turn out to be very annoying in the long run and the short run as all my insert statements are formatted in the DD-MM-YYY format.

Thanks.

Posted 15-Feb-15 11:11am

Chris_Burns

Add a Solution

Comments

barneyman 15-Feb-15 18:01pm

this is a common misconception when handling dates in SQL - SQL itself does not NEED to understand the locale for a date - it just stores a generic dateval given to it by the client, either directly (shudder) or via sp's - conversely, it's up the client to present the data the way the client expects - remember 5% of the planet show the date backwards :)

So, your client piece should do the heavy lifting of ensuring the right format is interpreted before going in

also, good practice to stores datetimes as utc

PIEBALDconsult 15-Feb-15 18:17pm

I certainly hope you are not storing dates as strings.

2 solutions

Solution 2

It sounds that if you're using DATE or DATETIME data types in the table then the code at the calling side is not using parameters but makes the calls by concatenating values directly into the SQL statement. This would cause this exact problem.

If that is the case, my advice is that the calling code should be changed. If the parameters are used correctly then the SQL Server driver takes care that the date is sent to the database and read from it in proper format.

Posted 18-Feb-15 17:59pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-02-15T11:25:00

Solution 1

If you want to resolve problem while inserting data, please use: SET DATEFORMAT[^] before insert statement ;)

SQL

SET DATEFORMAT dmy;
INSERT ...

Posted 15-Feb-15 11:25am

Maciej Los

Comments

[no name] 15-Feb-15 17:36pm

Is it possible to just reset the entire format of the column itself, because it will be inserting alot of dates on the front end of the application, so its best if i set the entire column to a certain format

Maciej Los 15-Feb-15 17:40pm

It's not a matter of column, it's sql server settings. Use stored procedures to add data, rather than client programme.