Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1373

Mar 9th, 2009

Date format confusion

Expand Post »

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

Similar Threads

confusion with loops in C++
Need opinions on a database design in Database Design
Variable scope problem in C++
New Time Format in DaniWeb Community Feedback

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 10th, 2009

Re: Date format confusion

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.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 10th, 2009

Re: Date format confusion

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:

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]

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 10th, 2009

Re: Date format confusion

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.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 10th, 2009

Re: Date format confusion

Click to Expand / Collapse Quote 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.

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 10th, 2009

Re: Date format confusion

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

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);

and do the same for the other one

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 10th, 2009

Re: Date format confusion

Ok, I will revise my code to implement this feature at ALL places.

Thank you for your suggestions.

Lalit Kumar Barik
India

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Question about MS SQL server certificates

Next Thread in MS SQL Forum Timeline: Adding a new user to a localgroup without using xp_cmdshell

DaniWeb Message

Cancel Changes

User Name
Password