Date,DateTime picker,Sql severC#

Posted by Raja_89 under C# on 11/2/2015 | Points: 10 | Views : 846 | Status : [Member] | Replies : 5
Hi
I am saving selected date from datetime picker in winform application .After saving the date if i saw the saved value as 1905-06-09 00:00:00 What makes this
While debugging everything fine
I am using datetime picker in custom format (dd-MM-yyyy)




Responses

Posted by: Rajnilari2015 on: 11/3/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
You have to set both Format and CustomFormat of DatePicker.

 dateTimePicker1.Format = DateTimePickerFormat.Custom;

dateTimePicker1.CustomFormat = "dd-MM-yyyy";


For reading use

MessageBox.Show(dateTimePicker1.Value.Date.ToString("dd-MM-yyyy"));


Complete Example

using System;

using System.Windows.Forms;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "dd-MM-yyyy";

}

private void button1_Click(object sender, EventArgs e)
{

MessageBox.Show(dateTimePicker1.Value.Date.ToString("dd-MM-yyyy"));
}
}
}


--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 11/3/2015 [Member] Starter | Points: 25

Up
0
Down
Hai
Thanks for your support.
I tried your way
The actual issue is saving the datetime picker value to db [Sql server 2008 R2 ]

Code:



Commencement_Dt = dtofCommencement.Value.ToString("yyyy-MM-dd");

insert into Policy_Details(dt,Policy_No,Policy_Holdername,Plan_Term,Sum_Assured,Prem_Amt,DtofCmmencement,DtofMaturity)values('" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "','" + Policy_NotoDb + "','" + Policy_HdrtoDb + "','" + Plan_termtDb + "','" + Sum_assured + "','" + Premium_Amt + "', " + Commencement_Dt + " ," + Maturity_Dt + ");


Regards


Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 11/3/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Are u experiencing any error?Please provide a detail information...this will help us to answer you properly and in less time (:
Moreover, why not you are using stored procedure? This way of saving the record in the DB is highly in-secure and SQL injection vulnerable.

One problem that you may encounter while inserting the record in 'dd-MM-yyyy' format in SQL Server is may be of type conversion like
Declare @t table(Commencement_Dt Date)

Insert into @t Values ('23-11-2015')
Select * from @t

If you execute this you will encounter the below error

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.


Here, the value '23-11-2015' is in the format of dd-MM-yyyy.

There are a couple of work around which we will show you

a) Change the Storage type from DATE or DATETIME to VARCHAR

Declare @t table(Commencement_Dt Varchar(12))

Insert into @t Values ('23-11-2015')
Select * from @t

/*
Commencement_Dt
----------------------
23-11-2015
*/

b) Use the Convert/CAST function to convert the storage type.
Case for Date storage type
Declare @t table(Commencement_Dt Date)

Insert into @t Values (CONVERT(date, '23-11-2015', 105))
Select * from @t

/*
Commencement_Dt
----------------------
2015-11-23
*/

Case for DateTime storage type
Declare @t table(Commencement_Dt DateTime)

Insert into @t Values (CONVERT(DateTime, '23-11-2015', 105))
Select * from @t

/*
Commencement_Dt
----------------------
2015-11-23 00:00:00.000
*/

Hope this helps.

However, please be detailed from next time so that it becomes easy for other to understand the problem (:

--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 11/4/2015 [Member] Starter | Points: 25

Up
0
Down
Hai
Thanks for your support
I got a solution by changing field type

Regards


Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 11/5/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Glad that it has helped.In that case you please accept the answer

--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response