insert date in sql server as "dd-mm-yyyy" format

Question

0.00/5 (No votes)

See more:

i insert date and its inserted as "yyyy-mm-dd" format
but i want to insert as "dd-mm-yyyy" format
please tell me simple coding for
how to insert date in sql server as "dd-mm-yyyy" format

Posted 3-Feb-14 23:53pm

Manish Dalwadi

Updated 5-Mar-18 6:44am

Add a Solution

Comments

Kornfeld Eliyahu Peter 4-Feb-14 6:31am

Oh! That hurts! You actually proposes to use string for storing date value, just because of visual formatting issue! Why?! What the connection?!

10 solutions

Solution 10

CONVERT(VARCHAR(8), GETDATE(), 105)

Posted 5-Mar-18 6:45am

Rahulmishra011

Comments

Maciej Los 5-Mar-18 13:03pm

Few years too late...
If you want to avoid down-votes, you shouldn't post an answer to such old (and resolved) question.

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)

Pete O'Hanlon · Accepted Answer · 2014-02-04T00:41:00

Don't insert a date as a specific format. Dates should be stored using the appropriate Date/DateTime types, and they should be stored as a universal format. It's a common misconception that you should be storing dates in a particular format - this is a bad practice to get into. What date was 10/11/2013? Was it the 10th November 2013 or the 11th October 2013? By storing the date in a universal format, you remove ambiguity and you then do the date formatting work at the place it should be done - at the UI after you have retrieved it, where it can be made to follow local date/time conventions.

Kornfeld Eliyahu Peter · Accepted Answer · 2014-02-04T00:37:00

SQL date is a binary field (stores binary value regardless of formatting).
So you do not store date in SQL by any format - only displays it in your desired format in your application.
you may use this at the SQL level - http://technet.microsoft.com/en-us/library/hh213505.aspx[^]
or simething like this inside the application
http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx[^]

CPallini · Accepted Answer · 2014-02-04T00:12:00

Solution 5

Unless you are inserting it as a string (varchar & friends) the date is not formatted, i.e. the internal representation has no notion of 'format'.
Probably you are performing a wrong conversion either from string to date or from date to string in your code.

Posted 4-Feb-14 0:12am

CPallini

**Dnyaneshwar**@Pune · Accepted Answer · 2014-02-04T00:32:00

Solution 6

CONVERT(date, @Date, 105)

Use this expression to convert date before insert.

Posted 4-Feb-14 0:32am

Dnyaneshwar@Pune

Comments

Ankur^\m/ 4-Feb-14 6:38am

How is it different from my answer. What's the point posting the same answer again?

Karthik_Mahalingam · Accepted Answer · 2014-02-03T23:58:00

Solution 1

C#

DateTime dt = DateTime.Now;
        string format = "dd-mm-yyyy";
        string dtTObeInsertedInSQL = dt.ToString(format);

Posted 3-Feb-14 23:58pm

Karthik_Mahalingam

Comments

Kornfeld Eliyahu Peter 4-Feb-14 6:32am

And - it still binary in SQL (it's a date)!!!

Ankur^\m/ · Accepted Answer · 2014-02-04T00:05:00

Solution 4

This format is not supported for insert in SQL Server date field by default. You will need to use CONVERT to do it

SQL

INSERT INTO TableName (DateColumn) VALUES (CONVERT(date, '13-02-2014', 105))

105 refers to style. Check this for more details - CAST and CONVERT (Transact-SQL)[^]

Hope that helps!

Posted 4-Feb-14 0:05am

Ankur^\m/

Comments

Ankur^\m/ 4-Feb-14 6:37am

The down-voter here, could you please give a reason for the down-vote?

Manish Dalwadi 4-Feb-14 6:47am

"@date date" is my stored procedure's parameter and "dob" is my table's field
and i tried it but showing me an error.

INSERT INTO birthdate (dob) VALUES (CONVERT(@dob, 'dd-mm-yyyy', 13));

i know i am wrong...
its first time for me...
sir, so pls avoid my mistakes

Ankur^\m/ 4-Feb-14 6:54am

It should be:
INSERT INTO birthdate (dob) VALUES (CONVERT(date, @dob, 13));

Just confirm the style no.

Manish Dalwadi 4-Feb-14 6:57am

i use it as
INSERT INTO birthdate (dob) VALUES (CONVERT(date, @dob, 13));
..
query is complete but date is inserted as
1990-02-02

and i want it as 02-02-1990.
.
pls help

King Fisher · Accepted Answer · 2014-02-04T00:00:00

Solution 2

Format

Posted 4-Feb-14 0:00am

King Fisher

Abhinav S · Accepted Answer · 2014-02-04T00:02:00

Solution 3

CONVERT - SQL Server CONVERT() Function[^].

Posted 4-Feb-14 0:02am

Abhinav S

v2

K.Y.S.Phaneendra · Accepted Answer · 2014-12-24T04:06:00

Solution 9

Try This

SELECT CONVERT(VARCHAR(10),GETDATE(),103)

By changing the last number you can get different formats of dates..

Posted 24-Dec-14 4:06am

K.Y.S.Phaneendra

Updated 24-Dec-14 4:09am

v2

insert date in sql server as "dd-mm-yyyy" format

10 solutions

Solution 8

Solution 7

Solution 5

Solution 6

Solution 1

Solution 4

Solution 2

Solution 3

Solution 9

Solution 10

Add your solution here

Preview 0