VS Date Format Question C# 2008

What is the proper way to code this which would allow the user to enter the date as 04/09/2011? Right now they have to type 2011/04/09. Do I simply need to do character extractions and move the characters around or is there a date format I can apply that will accept formats?
try
            {
                StartDate = Convert.ToDateTime(txtStartDate.Text).ToString("yyyyMMdd").ToString() + " 00:00:00.000";
                EndDate = Convert.ToDateTime(txtEndDate.Text).ToString("yyyyMMdd").ToString() + " 23:59:59.997";
               
                SqlConnection DataConnection = GetConnection();

                /* Define record set for query results of inventory items */
                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;

                /*DataCommand.CommandText = "SELECT rtrim(mc.PayerCode) as IDInsurCompany,rtrim(nv.PID) as PID,rtrim(nv.IDPatInsur) as IDPatInsur,rtrim(nv.FirstName) as Firstname,rtrim(nv.LastName) as LastName,rtrim(convert(char(10),nv.DOB,101)) as DOB " +
                "from [dm_asarum_OPX]..[dmpatinsur] nv, [mappingcodes]..[MapCodes] mc " +
                "where nv.IDInsurCompany = mc.navcode " +
                "order by nv.IDPatInsur"; */

                /* "dmAppt.ApptDateTime between convert(datetime,'" + StartDate + "',101) and " + "convert(datetime,'" + EndDate + "',101) AND dmAppt.IDApptStatus=1 " +
                     "convert(char(8),dmAppt.ApptDateTime,112)>=convert(char(8),convert(datetime,'" + StartDate + "'),112) and " +
                                          "convert(char(8),dmAppt.ApptDateTime,112)<=convert(char(8),convert(datetime,'" + EndDate + "'),112) AND dmAppt.IDApptStatus=1 " + */
                /* "Coalesce(dmprovid.provid,'0') as Provid," + */



                DataCommand.CommandText = "SELECT  CASE WHEN MapCodes.PayerCode IS NULL THEN '0' ELSE MapCodes.PayerCode END AS PayerCode," +
                                          "Coalesce(dmprovid.provid,'0') as Provid," +
                                          "Coalesce(dmPatInsur.PolicyNumber,'0') AS PolicyNumber," +
                                          "Coalesce(dmPatientDemo.FirstName,' ') AS FirstName," +
                                          "Coalesce(dmPatientDemo.MiddleInitial,' ') AS MiddleInitial," +
                                          "Coalesce(dmPatientDemo.LastName,' ') AS Lastname," +
                                          "CASE WHEN dmPatientDemo.DOB IS NULL THEN '' ELSE convert(char(10),dmPatientDemo.DOB,101) END AS DOB," +
                                          "convert(char(10),GETDATE(),101) AS ApptDate," +
                                          "convert(char(10),dmAppt.IDLocation) AS IDLocation " +
                                          "FROM ((([dm_asarum_OPX]..[dmAppt] dmAppt INNER JOIN [dm_asarum_OPX]..[dmPatientDemo] dmPatientDemo ON dmAppt.PID=dmPatientDemo.PID) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmPatInsur] dmPatInsur ON dmAppt.PID=dmPatInsur.PID) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmProvider] dmProvider ON dmAppt.IDApptResourceRendering=dmProvider.IDApptResource) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmProvId] dmProvId ON dmProvider.IDProvider=dmProvId.IDProvider	" +
                                          "LEFT OUTER JOIN [mappingcodes]..[MapCodes] MapCodes ON dmPatInsur.IDInsurCompany=MapCodes.NavCode " +
                                          "WHERE  dmPatInsur.Inactive=0 AND dmProvId.IDGenProvIDType=13 AND dmPatInsur.IDGenRelationship=1 AND " +
                                          "dmAppt.ApptDateTime between convert(datetime,'" + StartDate + "',101) and " + "convert(datetime,'" + EndDate + "',101) AND dmAppt.IDApptStatus=1 " +
                                          "order by dmAppt.ApptDateTime,dmPatientDemo.LastName,dmPatientDemo.FirstName,dmPatientDemo.MiddleInitial";

                /* MessageBox.Show(DataCommand.CommandText);  */
                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                _commandBuilder = new SqlCommandBuilder(DataDataAdapter);

                DataDataAdapter.Fill(DataDataSet);

                dt = DataDataSet.Tables[0];
                
                foreach (DataRow dr_loopvariable in dt.Rows)
                {
                    dr = dr_loopvariable;
                    DataString = "|" + dr["PayerCode"] + "|" + dr["Provid"] + "|" + dr["PolicyNumber"] + "|" + "|" + "|" + dr["FirstName"] + "|" + dr["MiddleInitial"] + "|" + dr["LastName"] + "|" + "|" + dr["DOB"] + "|18|" + "|" + "|" + "|" + "|" + "|" + "|" + "|" + "30|" + dr["ApptDate"] + "|" + "|" +dr["IDLocation"];
                    tw.WriteLine(DataString);
                }

                DataDataSet.Dispose();

                tw.Close();

            }
            catch (Exception ex)
            {
                string eMsg = "002: ERROR: " + ex.Message;
                if (stackTraceWanted) eMsg += "\n" + ex.StackTrace;
                MessageBox.Show(eMsg);
            }

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheMozzCommented:
You could use DateTime.Parse when getting the dates from your text boxes. DateTime.Parse is able to parse many different formats into a DateTime object, which you can format into many different string representations at will. Check out: http://msdn.microsoft.com/en-us/library/1k1skd40.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gery128Commented:
0
rwheeler23Author Commented:
Thank you. Both of these work but there must be something wrong with my SQL statement. If I just want the records of 09/03/2009 I have discovered I need to enter 09/03/2009 as the start date and 09/04/2009 as the end date. How do I get the between to work if I only want one specific date?
0
gery128Commented:
for specific date, you can convert the date to char(10) and then put the where condition:




SELECT Some_Date_Column FROM Table1
WHERE CONVERT(CHAR(10),Some_Date_Column,120) = '2009-09-04'

This will convert your datetime column's date to specific char(10) format and match string then.
0
rwheeler23Author Commented:
That is true, but that means I have to have two SQL statements. One with the between and one without. Perhaps I will simply not use bertween and just use >= and <=
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.