MS SQL

MS SQL RSS

Date format confusion

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Date format confusion

Mar 9th, 2009

I have an ASP.net 2.0 website with SQL Server as database and C# 2005 as the language. On my webpage I have a calender from which I choose a date and display only the date part in a textbox (readonly) in the format dd/mm/yyyy. But internally SQL Server uses m/d/y date format. I am using parameterised queries to insert and select records. But when I checked the database using the management tool and link supplied to me by the hosting service, I found that a few records have been saved in dd/mm/yyyy format while the rest have been saved the the default mm/dd/yyyy format. I suspect, it happened when I made some changes in the date format handling code and saved some records. But since the no. of records are not many, I am ready to delete all existing records and use the entry page again and add ALL records. But how should I ensure that even if I display the date in dd/mm/yyyy format on my webpage it gets saved in only one format (either m/d/y or d/m/y). The date format is very important to me, because most of the time the records will be fetched based on a specific date.

Is there any way, by which I can choose the date format to be d/m/y while saving and retrieving the records from the database??

Lalit Kumar Barik
India

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Date format confusion

Mar 10th, 2009

Are you sure you set the column's data type to datetime? It sounds like you are using varchar

Last edited by dickersonka; Mar 10th, 2009 at 12:55 pm.

Custom Application & Software Development
www.houseshark.net

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Re: Date format confusion

Mar 10th, 2009

The RDate field's datatype is smalldatetime. My requirement is that a calender is displayed on the page along with a collection of times (displayed in a dropdown list and which contains values like 09:00 AM, 09:30 AM, etc )for the RTime field. The record contains RDate, DTime and other values. For the same RDate, there can be multiple records each having a different RTime. No two records can exist with the same combination of RDate & RTime. I am facing problems in implementing this simple requirements. When the user chooses a date from the calender, I am storing it in a DateTime variable as it is and also displaying it in a readonly textbox in the format "dd/MM/yyyy" for user's reference. While inserting the record I am checking against the DateTime variable and also using that value in the record. But I am facing problem while checking for unique record. Even if I change the Date by choosing a different date from the calender and select the same RTime value as an earlier choosen one, I am getting an error for duplicate entry. My code to check duplicates is as follows:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 
DateTime dtCurrentDate ;
CalendarRDate.SelectedDate = DateTime.Today;
dtCurrentDate = CalendarRDate.SelectedDate;
 
 
string strConnection = ConfigurationManager.ConnectionStrings["SuperConnString"].ConnectionString;
string strSQLQueryCheckDuplicates = @"Select Count(*) from Results where RDate = @RDate And RTime = @RTime";
 
        SqlConnection sqlConnCheckDuplicates = NULL;
 
        try
        {
            sqlConnCheckDuplicates = new SqlConnection(strConnection);
            sqlConnCheckDuplicates.Open();
 
            SqlCommand sqlCmdCheckDuplicates = new SqlCommand(strSQLQueryCheckDuplicates, sqlConnCheckDuplicates);
            sqlCmdCheckDuplicates.Parameters.ADD(new SqlParameter("@RDate", dtCurrentDate));
            sqlCmdCheckDuplicates.Parameters.ADD(new SqlParameter("@ResultTime", ddRTime.Text.Trim()));
 
            IF (Convert.ToInt32(sqlCmdCheckDuplicates.ExecuteScalar()) > 0)
            {
                lblMessage.Text = "Results for choosen timeslot has been entered already";
                ddRTime.Focus();
                RETURN;
            }
            else
                lblMessage.Text = string.Empty;
        }
        finally
        {
            sqlConnCheckDuplicates.Close();
        }
DateTime dtCurrentDate ;
CalendarRDate.SelectedDate = DateTime.Today;
dtCurrentDate = CalendarRDate.SelectedDate;


string strConnection = ConfigurationManager.ConnectionStrings["SuperConnString"].ConnectionString;
string strSQLQueryCheckDuplicates = @"Select Count(*) from Results where RDate = @RDate And RTime = @RTime";

        SqlConnection sqlConnCheckDuplicates = null;

        try
        {
            sqlConnCheckDuplicates = new SqlConnection(strConnection);
            sqlConnCheckDuplicates.Open();

            SqlCommand sqlCmdCheckDuplicates = new SqlCommand(strSQLQueryCheckDuplicates, sqlConnCheckDuplicates);
            sqlCmdCheckDuplicates.Parameters.Add(new SqlParameter("@RDate", dtCurrentDate));
            sqlCmdCheckDuplicates.Parameters.Add(new SqlParameter("@ResultTime", ddRTime.Text.Trim()));

            if (Convert.ToInt32(sqlCmdCheckDuplicates.ExecuteScalar()) > 0)
            {
                lblMessage.Text = "Results for choosen timeslot has been entered already";
                ddRTime.Focus();
                return;
            }
            else
                lblMessage.Text = string.Empty;
        }
        finally
        {
            sqlConnCheckDuplicates.Close();
        }

Where am I making the mistake??

Lalit Kumar barik
India

Last edited by peter_budo; Mar 18th, 2009 at 7:23 am. Reason: Please use [code][/code] instead of [quote][/quote]

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Date format confusion

Mar 10th, 2009

For starters lets start off with syntax:

Did you see this line?
SqlParameter("@ResultTime", ddRTime.Text.Trim()));
your parameter is named @RTime

Last edited by peter_budo; Mar 18th, 2009 at 7:24 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Custom Application & Software Development
www.houseshark.net

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Re: Date format confusion

Mar 10th, 2009

•

Originally Posted by dickersonka

For starters lets start off with syntax:

Did you see this line?
SqlParameter("@ResultTime", ddRTime.Text.Trim()));

your parameter is named @RTime

I acknowledge my mistake. But in the real life coding it was rectified already. I didn't notice it when I did some copy paste job.

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Date format confusion

Mar 10th, 2009

Ok, just wanted to make sure we aren't chasing a ghost

Rather than just adding the parameters with a name and value, try to add them with a name and type

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SqlParameter param1 = new SqlParameter("@RDate", SqlDbType.DateTime);
param1.Value = dtCurrentDate;
sqlCmdCheckDuplicates.Parameters.ADD(param1);
SqlParameter param1 = new SqlParameter("@RDate", SqlDbType.DateTime);
param1.Value = dtCurrentDate;
sqlCmdCheckDuplicates.Parameters.Add(param1);