How To ( Change ) Convert Date Format When I Stored In Sql Server From Datepicker

Question

3.00/5 (1 vote)

See more:

SQL-Server-2008R2

i am working in a project , where i have DATEPICKER in that,I selected the DATE and stored in SQLSERVER .. Its working FINE .. BUT i want DD-MM-YYYY format ..

so in query am using convert(varchar(50),@date,105) .But CONVERT function didn't work..it stored as date and time only ..PLease rack me out ...Thanks in advance..

Posted 10-Dec-14 0:04am

Bns Vigneshwaran

Add a Solution

Comments

Thanks7872 10-Dec-14 6:11am

Date doesn't have format. Its Date's string representation which can be formatted. Your date&time.ToString("whatever format") is what you are looking for,i think.

Further, use proper datatypes while storing data into DB. e.g. DateTime for storing datetime values.

Bns Vigneshwaran 10-Dec-14 6:16am

am using datepicker1.value while inserting into database... So obviously am getting answer in date time format .... so when insert i queried as convert(varchar(50),datepicker1.value,105) <---- it makes nothing .. So please help me to achieve...

Thanks7872 10-Dec-14 6:25am

If you somehow got DateTime,why converting it then? Store it to db without any processing. Retrieve the same way at time of display. This time, you just need to convert to whatever format you want (ToString).

4 solutions

Solution 1

SQL dates don't have a format - they (just like the C# DateTime structure) are stored as a number of milliseconds since a specific point in time. And they only ever acquire a "format" such as dd-MM-yyyy or MM/dd/yyyy when they are presented to the user (which requires a string conversion). This conversion to a formatted strign is done as late as possible (and conversion from a user input string done as early as possible) so that it can be presented in the format the user prefers, and specified by his systems selected Culture. Since SQL doesn't know what PC (or tablet, or phone) the data is going to be displayed on, it is a bad idea to store info in a formatted strings, or even to convert it to a string at the SLQ layer - it needs to be done at the presentation layer, be that a Winforms form or a website.

So pass your DateTime value from the date picker directly to SQL via a parametrised query, and return your stored values as DateTime to your C# code for formatting there.

Posted 10-Dec-14 0:18am

OriginalGriff

Solution 4

hi,

use this

select convert(date,cast(@date as datetime),103)

date format-- dd/mm/yyyy-->>103

Posted 10-Dec-14 0:32am

mudgilsks

Updated 10-Dec-14 0:33am

v2

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)

/\jmot · Accepted Answer · 2014-12-10T00:24:00

That's right.. "SQL dates don't have a format"
if you want to show SQLDB date time in text box, then you must convert it to the String..

Use..

C#

textbox1.Text=(DbdatetimeCoumnValue).toString("dd-MM-YYYY")

For C# Custom Date and Time Format Strings
see
http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx[^]

Praveen Kumar Upadhyay · Accepted Answer · 2014-12-10T00:23:00

Solution 2

Simply convert your DatePicker value to String and then store it to database.

C#

string date = Convert.ToDateTime(datePicker.Value).ToString("dd-MM-yyyy");

Posted 10-Dec-14 0:23am

Praveen Kumar Upadhyay

Comments

Praveen Kumar Upadhyay 10-Dec-14 6:37am

Wow, someone has devoted and someone has Upvoted my solution.

Bns Vigneshwaran 10-Dec-14 6:39am

You Genius !! Thanks for ur help ..

Praveen Kumar Upadhyay 10-Dec-14 6:42am

Use below code. If you are using Datepicker in Windows Application

datepicker1.Value = Convert.ToDateTime(dr["h_dob"].ToString());

Bns Vigneshwaran 10-Dec-14 6:43am

Thank you genius

Praveen Kumar Upadhyay 10-Dec-14 6:44am

Welcome buddy and thank you for accepting the answer and I am not Genius just trying to solve few small small issue here.

Bns Vigneshwaran 10-Dec-14 6:51am

I got error as string does not recognize ... Please help me

Praveen Kumar Upadhyay 10-Dec-14 6:52am

Where did you get this Error.

Bns Vigneshwaran 10-Dec-14 6:53am

datepicker1.Value = Convert.ToDateTime(dr["h_dob"].ToString());

in this line only

Praveen Kumar Upadhyay 10-Dec-14 6:57am

What is the value of dr["h_dob"].ToString(). Can you please debug.

Bns Vigneshwaran 10-Dec-14 6:56am

System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(string s,DatetimeFormatInfo dtfi, DateTimeStyles styles)

CHill60 10-Dec-14 17:16pm

Use solution 1. Storing a date as a string in a database is an incredibly bad idea

CHill60 10-Dec-14 17:14pm

Use appropriate data types to store data. String is NOT the appropriate data type in which to store a date!