unable to update datetimepicker date values sqlserver database.

Posted on 2009-02-11
Last Modified: 2012-05-06
I tried to update one table with :
update StatusOfFittings set FittingCompletedDate = '05-02-2009 PM 03:13:50' where FittingName = 'WBSTDWTzxcvbn'

Here FittingCompletedDate is the datetime Datatype.
I am using DatetimePicker control in C#-winforms front end.
ditrectky i took the date like: FittingCompletedDate = 'datetimepicker.value.ToString()'
I got exception: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

One of Expert given this comment before:

pratima_mcs:try something like this

 FittingCompletedDate = 'datetimepicker.value.ToString("yyyy-MM-dd hh:mm:ss")

I changed like this, but I able to update only time, changes of am to pm . per time: '05-02-2009 PM 03:13:50'  but it updates '05-02-2009 AM 03:13:50'  like this.

Plz help on this. I want to update exact timing into the sql database.

Question by:raghu_apps
    LVL 39

    Expert Comment

    by:Pratima Pharande
    I am not getting =teh exact problem tell me
    if you try like this directly will it work ?

    update StatusOfFittings set FittingCompletedDate = '06-03-2009 PM 03:13:50' where FittingName = 'WBSTDWTzxcvbn'
    LVL 26

    Expert Comment

    by:Anurag Thakur
    change the following
    FittingCompletedDate = datetimepicker.value.ToString("yyyy-MM-dd HH:mm:ss")
    LVL 26

    Expert Comment

    by:Anurag Thakur
    your codes is
    FittingCompletedDate = datetimepicker.value.ToString("yyyy-MM-dd hh:mm:ss")

    and mine is
    FittingCompletedDate = datetimepicker.value.ToString("yyyy-MM-dd HH:mm:ss")

    i have changed hh to HH which takes 24 hour clock and not 12 hour clock

    Author Comment

    That is Correct. But I need to update as 12-hr format including am and pm.

    Here exact date and time was updated instead am to pm or pm to am (means it updated 12-hours back).

    I want to update exact date time as per our system time. It is mandatory for me.. I am using GMT+5:30 time.
    plz advice.

    LVL 29

    Expert Comment

    by:Gautham Janardhan
    u can format that once in the application. in sql it will store in 24 hr format
    LVL 29

    Expert Comment

    by:Gautham Janardhan
    or else u need to store it as string in the DB which is not advisable
    LVL 26

    Accepted Solution

    sql always stores it in 24 hour format and when you return the value to the place where you want to use you can format the values then

    you can use the following
    Show date in 12H format with AM/PM as: "Friday 13 August 01:00:13 PM"
    date.DayOfWeek + " " + date.Day + " " + date.ToString("MMMM") + " " + date.ToString("hh:PM:mm:ss:tt")

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Article by: Ivo
    Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now