Hi, I'm having some terrible troubles with inserting DateTime into an MSSQL database and being able to search for data by date. It's all confusing me because of the damn US-style date formatting (no offence).
I have a form that users fill out, and one piece of data happens to be the date (dd/mm/yyyy). I'm using a regex to make sure it can only be entered in the correct format. Now, I know that SQL doesn't have a 'Date only' datatype so I'm using the 'datetime' data type. When the user clicks Process, I use DateTime date = Format.ToDateTime(txtDate.Text) and then insert 'date' into the DB.
When the date (say 11/10/2007) is inserted into the database, it shows up (in MS SQL Server Management Studio Express) as '11/10/2007 12:00:00 AM' which appears to be correct. I think it's correct because if I enter 31/01/2007 it shows up in SQL as '31/01/2007 12:00:00 AM', otherwise wouldn't it crash because there is no 01/31/2007?
Anyway, the insertion *seems* to be working correctly, but it's the search function that's driving me crazy. If a user wants to search for data by date, they enter the date into the textbox and again I format it by doing: DateTime searchDate = Format.ToDateTime(txtSearch.Text)
I then query the database with "SELECT * FROM tablename WHERE Date = '" + searchDate + "' but it doesn't return the correct results!
If I want to see the results for data on 11/10/2007, I have to search for 10/11/2007 otherwise there will be no results shown. Also, if I search for 31/01/2007, it crashes with an error saying something about converting a char to the datetime datatype out of range.
I have edited my Web.Config file so that it contains the globalisation tabs with my culture etc set to en-AU etc...
I'd post my code here, but I'm at home now and the code is at work. I'll post it here soon as I'm sure you'll need it.
Any help is *greatly* appreciated. This is really annoying...DateTime in .NET and SQL just don't mix for me.
When you insert dates into a database (and also whenever you deal with database dates), they should always be sent in the format yyyymmdd. All databases will deal with this format in the correct manner and it will alleviate any formatting issues.
The date searches in SQL can sometimes be tricky because SQL keeps time also with the date. Now, I've observed that when matching a date for = it can be useful to use convert function, but mind well this function actually converts the date to a varchar value and then compares 2 values, but sometimes it can be useful. The sample query you can use is as below.
SELECT *FROM tablenameWHEREconvert (varchar(20) , Date , 103 ) =convert (varchar(20) ,'<textbos date>' , 103 )
If you can post some sample data, code and expected results then we'll be able to solve your problem better.
Hope this will help.
hmm, so to do that i'd take the value from the text box (11/10/2007), convert it to DateTime (DateTime time = Convert.ToDateTime(txtDate.Text), and then what?
How do I make it yyyy/mm/dd?
schuminator:
How do I make it yyyy/mm/dd?
If you have a date in a TextBox, you can just use:
TextBox1.Text.ToString("yyyyMMdd") Make sure you validate it first though to make sure it is a valid date
|||TextBox1.Text.ToString("yyyyMMdd") and then convert to datetime, or just insert into db as a string? (with datetime as the format in sql)?|||Just add it to the Parameter (which presumably you will have defined as a date) in your database call or add it as a String if it's part of a SQL Statement. If you are not using stored procedures and parameters, then you should as this will also help alleviate date problems.
Ok, txtDate.Text.ToString("yyyy/MM/dd") didn't work because it wasn't a valid IFormatProvider, but I fixed that by:
IFormatProvider format =new System.Globalization.CultureInfo("en-GB",true);
DateTime searchDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);
I had to do it in dd/mm/yyyy format because if I used yyyy/MM/dd I got this:
String was not recognized as a valid DateTime.
DateTime uploadDate = DateTime.ParseExact(txtDate.Text, "yyyy/MM/dd", format);
Anyway, the uploading of the date still appears to be working in that it appears correct in SQL Server, but retrieving data by the date is still a problem.
I am using SQL Statements and not Stored Procedures (SP). Although, while we're on the topic of SP, maybe you could help me out a bit? I've used them before, but I'm not very proficient so I have problems making ones that will actually be more advantageous than SQL Statements.
Here is how I'm adding data to the DB:
================
DateTime uploadDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);
Mail newMail =newMail(uploadDate, txtStrataPlan.Text.ToUpper(), txtRecipient.Text.ToUpper(), txtSuburb.Text.ToUpper(), txtDetails.Text.ToUpper(), envelope, stamp);
DataBaseModifier.uploadMailToDatabase(newMail);
staticSqlConnection conn;
publicstaticvoid uploadMailToDatabase(Mail mail)
{
conn =SqlLogin.SqlConnect;
SqlDataAdapter adapter =newSqlDataAdapter("SELECT * FROM tblMail", conn);
SqlCommandBuilder builder =newSqlCommandBuilder(adapter);
conn.Open();
DataSet ds =newDataSet();
adapter.Fill(ds,"tblMail");
DataTable table = ds.Tables["tblMail"];
DataRow row = table.NewRow();
int fileID = getFileID(table);
row["MailID"] = fileID;
row["Date"] = mail.getTime();
row["StrataPlanID"] = mail.getStrataPlan();
row["Recipient"] = mail.getRecipient();
row["EnvelopeCount"] = mail.getEnvelopeCount();
row["StampCount"] = mail.getStampCount();
row["Details"] = mail.getDetails();
row["Suburb"] = mail.getSuburb();
table.Rows.Add(row);
adapter.Update(ds,"tblMail");
conn.Close();
}
=========
And here is how I'm retrieving data from the database and putting it into a gridview:
=========
DataBaseModifier.retrieveMailFromDatabase("SELECT * FROM tblMail WHERE Date='" + uploadDate +"' ORDER BY 'StrataPlanID'", mailView);
publicstaticvoid retrieveMailFromDatabase(string selection,GridView grid)
{
conn =SqlLogin.SqlConnect;
SqlCommand command =newSqlCommand(selection, conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close();
}
As I said, the uploading seems to be working, it's just the retrieval that seems to be causing me problems in that to find data processed on 12/10/2007 I need to enter 10/12/2007.
If a stored procedure would alleviate these problems, any assistance or guidance in creating said stored procedure would be greatly appreciated.
Ok, I think I misunderstood the formatting command you mentioned. I managed to do it like this:
DateTime uploadDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);
string date = uploadDate.ToString("yyyyMMdd");
However, when I try to upload that string to SQL (using the methods posted above), I get this:
String was not recognized as a valid DateTime.
DataBaseModifier.uploadMailToDatabase(newMail);
I'm a complete newbie when it comes to stored procedures etc
Ok I got the search function working by changing string searchDate = date.ToString("yyyyMMdd") to:
string searchDate = date.ToString("yyyy-MM-dd");
I'd still like to be using stored procedures if it's going to be 'better', so any help would be awesome.
Thanks
Yes I also face this problem.
I think, your point is like this.
User always in put the date format [dd/mm/yyyy] Right ?
So, you need to try to convert to date format.
--
Dim sDate as Date
sDate = CDate(Me.txtSendDate.Text)
--
and then you can you in user query string.
" WHERE docdte ='" & sDate & "'"
and normally SQL Server datetime format is yyyymmdd.
so, you just put this statement before execute your statement
SET DATEFORMAT dmy
so, your select statement happen like this
SQLSTR = " SET DATEFORMAT dmy " & _
" SELECT * FROM yourtable WHERE yourdatefield ='" & sDate & "'"
This is just for simple SELECT statement, if you try to send the parameter to s procedure you can use like this
-------
ALTER PROCEDURE sp_xxxxx
@.sDate nvarchar(20)
AS
SET DATEFORMAT dmy
SELECT * FROM yourtablename WHERE yourdatefield = @.sDate
GO
-------
and from your client side, you can execucte like this.
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
string sqlstr = "EXEC your_procedurename '" & sDate & "'";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = sql.Conn;
cmd.ExecuteNonQuery();
No need to use any parameter object.
arr... so my code is change to C# :) anyway I hope you can change to your code.
do you aware ? in my simple s-procedure, for date parameter, i just use the nvarchar.
as my experience datetime datatype is give alot of problem to me. So, if possible i never you this datatype in parameter.
and then you need to set the culture setting in your IIS.
Goto ASP.Net Configuration Setting and the select the Application Tab
you will see Culture setting set like this
Culture = en-GB
UI culture = en
That's all. I hope you can solve this problem.
If my solution is not complete or not in timely, I am sorry for it.
Soe Thiha.
No comments:
Post a Comment