asked on

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

ASKER CERTIFIED SOLUTION

TheMozz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

gery128

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

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?

gery128

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.

rwheeler23

ASKER

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 <=