insert date to sql

Question

hi all
i am new about in c# and making a small project to improve myself and to learn new things...but as u can imagine i have a problem...i have a datetimepicker in my form and i keep the customer's birthdate with it...but it tries to save the birthdate to database like dd.mm.yyyy...i use sql server 2005 express edition for database and it keeps that data as yyyy.mm.dd so how can i get rid of that problem..i want it to keep data as dd.mm.yyyy...and i keep my data in database as smalldatetime...i dont want to keep it as string because maybe i will make some operations on it later...thanks for your help

Rekha Singh · Answer

u can try it out this way.. when u r inserting it in to ur table u can convert it and then insert declare @birthday as datetime -- pass ur birthday date from ur datepicker to this variable from ur front end set @birthday = getdate () insert into table_name ( column_name ) values ( convert ( char ( 11 ) @birthday  113 ) ) output will be : 29 Dec 2009 ***************************************************************************** Please mark it Accepted if u find it helpful.

Kirtan Patel · Answer

Friend Here I m giving your Solution if my answer helps you then check Do you like this answer check box :) you dont need to do any thing with the code or no need to Convert Column Data type to varchar etc :) just go to Control Panel >> Regional Settings >> Change the Date Format there after that SQL Server will change the DateFormat Automatically in data:) See below Screen Shot

Erdinc Kolukisa · Answer

thanks manoj i will try your codes

manoj kumar · Answer

Hey.... First create table in database datatype should be datetime create table date ( DateOfBirth datetime ) and write code on button event: string database=ConfigurationManager.ConnectionString[you declred in webfile stringName].connectionString; SqlConnection con = new SqlConnection(database); try { con.Open(); SqlCommand cmd = new SqlCommand(Insert into date(datetime)values(@datetime)); cmd.Parameter.Add(@datetimesqldbtype.datetime).values=txtdatetime.Text(); cmd.ExecuteNonQuery(); response.write(true); } catch(Exception ex) { throws ex; } Finally { con.Close(); } } May be helpfull for you...

theLizard · Answer

Forget all the other attempts at answering your question the reality is that how it is being stored in 2005 Express is absolutely correct. If you want to store dates the way you want to then you have to define a VARCHAR or NVARCHAR in your database then you can store your dates any way that you want to BUT then you will need to handle ALL date conversions to EVERY region that your application may execute in. The reason that dates should be stored yyyy-mm-dd is really really obvious it is done this way to eliminate ALL the problems like having to find out what region the computer is in and reading regional settings and testing for how the user enters dates and how you will display dates and how you will save them. It is not that difficult to convert a date to a string formatted to the way that you want to see it you just need to learn how to do it programatically it is really very simple...

Kirtan Patel · Answer

Hi Friend Have you restarted Computer After changing The Regional Setting ??? and also note that you need to change Both Sort Date Format and Long Date Format After that Restart the Computer and Then see DateColumn in SQL Server :) Hope it will help you if my answer helps you then check do you like this answer check box :) if still not resolved then let me know :)

Erdinc Kolukisa · Answer

hii hiren i tried your way as well.. but still i dont get the format as i wish as i see our codes are almost similar i dont know if i should write some codes in sql server ??

Erdinc Kolukisa · Answer

hiii kirtan thanks for the reply i tried what i see in the screenshot and my regional setting already same with the screenshot view but still i see the dateformat like 2009.12.29 any other suggestion u have ?

Hiren Soni · Answer

Erdinc Kolukisa · Answer

thanks for the reply man but we would use here the date as string isnt it...so i have to convert it to DateTime when i want to compare to dates or i am wrong ??