Hi Jayshree,
SQL Server takes yyyy/MM/dd format to insert/update in sql Query as string
1st convert row[21].ToString() value apply format to this value.
Strings.Format(row[21].ToString(), "yyyy/MM/dd");
Or
Use CONVERT(DateTime,DateValue,103)
You can use the CONVERT function directly in your query.
It is not advisable to store the DATETIME data in any other format, other than what is defined in your SQL-Server database.
A better approach is to store the datetime data in SQL-Server in its default format (say mm/dd/yyyy). And when you want to display in your application, you have many ways of showing data in your desired format.
For example:
Using SQL-server query
select convert(varchar,myDateField,101) as myDateField from tbDetails
Keep changing the value 101 to 102,then 103 and son so on to see the difference...
Inside the code
string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd/MMM/yyyy");
string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd/MM/yyyy");
string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd-MMM-yyyy");
string myDateofBirth = Convert.ToDateTime(row[21]).ToString("MM/dd/yyyy");
and so on...........
Still if you want to use approach of saving the datetime in dd//mm/yy format, you can do the following:
Jayshree
Member
95 Points
100 Posts
how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:14 AM|LINK
how to save data in datetime datatype but in dd/mm/yy format...
i was passed value in dd/MM/yyyy format...
e.g. 28/5/2011
cmd.Parameters.AddWithValue("@BIRTHDAY",(row[21].ToString()));
cmd.Parameters["@BIRTHDAY"].Direction = ParameterDirection.Input;
Thanks in advance...
jayshree
brijeshvaidy...
Participant
886 Points
248 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:16 AM|LINK
hope this link will help you,
http://forums.asp.net/t/1446308.aspx/1
Brijesh Vaidya
India
Sunny Jagtap
Member
295 Points
112 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:23 AM|LINK
Strings.Format(row[21].ToString(), "yyyy/MM/dd");Sunny
jayakumarv
Member
2 Points
21 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:37 AM|LINK
hi jayshree,
please see this site for all date conversion.
http://www.sql-server-helper.com/tips/date-formats.aspx
Regards,
jai.
oned_gk
All-Star
36192 Points
7370 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:44 AM|LINK
I dont know what you mean :
how to save data in datetime datatype but in dd/mm/yy format...
i was passed value in dd/MM/yyyy format...
the database not save datetime format.
What i know, mm is menute and MM is Month
Suwandi - Non Graduate Programmer
munishchauha...
Member
99 Points
24 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 06:55 AM|LINK
It is not advisable to store the DATETIME data in any other format, other than what is defined in your SQL-Server database.
A better approach is to store the datetime data in SQL-Server in its default format (say mm/dd/yyyy). And when you want to display in your application, you have many ways of showing data in your desired format.
For example:
Using SQL-server query
Keep changing the value 101 to 102,then 103 and son so on to see the difference...
Inside the code
string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd/MMM/yyyy"); string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd/MM/yyyy"); string myDateofBirth = Convert.ToDateTime(row[21]).ToString("dd-MMM-yyyy"); string myDateofBirth = Convert.ToDateTime(row[21]).ToString("MM/dd/yyyy"); and so on...........Still if you want to use approach of saving the datetime in dd//mm/yy format, you can do the following:
cmd.Parameters.AddWithValue("@BIRTHDAY",Convert.ToDateTime(row[21]).ToString("dd/MM/yyyy"));Please remember that this approach will be successful only
- if your database is configured to accept time in this format
OR
- your datetime field in the table is of type varchar/nvarchar/text etc.
Simplicity is the ultimate sophistication
Jayshree
Member
95 Points
100 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 07:32 AM|LINK
Thanks for u r reply........
i used this code in my project but it show the error......
cmd.Parameters.AddWithValue("@BIRTHDAY", Convert.ToDateTime(row[21]).ToString("dd/MM/yyyy"));
cmd.Parameters["@BIRTHDAY"].Direction = ParameterDirection.Input;
This is error.....
Error converting data type nvarchar to datetime
Thanks in advance
jayshree
munishchauha...
Member
99 Points
24 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 07:36 AM|LINK
What is the DATATYPE you are using to store datetime value. Is it DateTime or something else. This will not work, if column is of DateTime type
Simplicity is the ultimate sophistication
Jayshree
Member
95 Points
100 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 07:38 AM|LINK
MM is month...
Jayshree
Member
95 Points
100 Posts
Re: how to save data in datetime datatype but in dd/mm/yy format...
Jul 04, 2012 07:42 AM|LINK
Datatype was datetime sir....
u have any idea sir to do this
thanks in advance...
jayshree