locked
DateTime format problem ( Local DateTime) RRS feed

  • Question

  •  Hi all I am updating sql server database from my vb.net application.

    It shows the date /DateTime in forms as per the local settings ( like mm-dd-yy or yy/mm/dd etc)

    Now the problem is when I update my sql table by format mm-dd-yy it is ok but when I update when I am using format "yy/mm/dd" and let us suppose date is "11/08/16"(16-aug-2011) and select in sql server it gives me 2016-11-08 ( 2016-Nov-08) .

    I found the solution for it " use update/insert query and convert the datetime to a fixed format before saving/updating and it works

    but I have instances where I am using dataadapter.Update method with datatable where I am updating multiple tables and it creates the problem again 

    As when I set any fixed format to my datatable column it reset it to local system format. eg if i set the values to "MM-dd-yyyy" format it again sets it to my local system format like (yy/MM/dd)

     

    I already checked for same in google and found no good results>?

    Any one of you have worked on the same ?

    or Is there any way to change the datetime format of my datatable datetime column?

    I changed it string and tried it but it creates problem on updating ("can not convert string to datetime")

     

    Thanks

     

     

     

     

     

     


    Want to add MVP with my name.
    Tuesday, August 16, 2011 3:15 PM

Answers

  • In my opinion, storing the datetimes as strings is a backwards step.

    You can use DateTime.Parse or DateTime.ParseExact to convert from the user's input into a DateTime, and use sql parameters to pass DateTimes to the database.

    --
    Andrew

    • Proposed as answer by SJWhiteley Wednesday, August 17, 2011 7:35 PM
    • Marked as answer by SJWhiteley Monday, August 22, 2011 11:55 AM
    Tuesday, August 16, 2011 5:54 PM
  • Your database field should be a date field. Is this correct? You should be manipulating date data types only.

    If this is true, then you should have no problems. A date is a date, format is irellavant, and as Andrew initially stated, it is different from the representation of that date - which can be pretty much anything.

    When your 'user' types in a date (as a string) you need to convert it appropriately to a date, as, again, Andrew noted.


    Stephen J Whiteley
    • Marked as answer by bahushekh Thursday, August 18, 2011 10:30 AM
    Wednesday, August 17, 2011 7:35 PM

All replies

  • It appears that you need to get used to the idea of the difference between a date and the representation of that date as a string. If you always work with datetime in your code (and database) then your problem will only be at the UI converting to and from strings.

    --
    Andrew

    Tuesday, August 16, 2011 4:16 PM
  • Hi sir thanks for replying I think you are correct. But I cant( or have to make huge changes) change the way of saving ( datatable update using dataadapter ) .

    Thanks


    Want to add MVP with my name.
    Tuesday, August 16, 2011 4:19 PM
  • You have a choice: make those huge changes or suffer in the future every time you want to modify the program or query the database ;)

    --
    Andrew

    Tuesday, August 16, 2011 4:22 PM
  • Hi sir Thanks again . I got another solution like change the datetime column format is database to string and save like 20110816 in it and change code for saving and retrieving but dont know either it is feasible or not . pls suggest.
    Want to add MVP with my name.
    Tuesday, August 16, 2011 4:25 PM
  • In my opinion, storing the datetimes as strings is a backwards step.

    You can use DateTime.Parse or DateTime.ParseExact to convert from the user's input into a DateTime, and use sql parameters to pass DateTimes to the database.

    --
    Andrew

    • Proposed as answer by SJWhiteley Wednesday, August 17, 2011 7:35 PM
    • Marked as answer by SJWhiteley Monday, August 22, 2011 11:55 AM
    Tuesday, August 16, 2011 5:54 PM
  • Hi Sir thanks again. If I am starting this than ofcouse your post is my answer. It is really helpful but still I am not making your post as answer hoping some other replies within 1 or two days.

    Can you please explain me why datatable in dataset and sql server do these conflicts as both are microsoft technologies and it should be in sync ?

     

    Thanks again 


    Want to add MVP with my name.
    Wednesday, August 17, 2011 2:37 PM
  • Your database field should be a date field. Is this correct? You should be manipulating date data types only.

    If this is true, then you should have no problems. A date is a date, format is irellavant, and as Andrew initially stated, it is different from the representation of that date - which can be pretty much anything.

    When your 'user' types in a date (as a string) you need to convert it appropriately to a date, as, again, Andrew noted.


    Stephen J Whiteley
    • Marked as answer by bahushekh Thursday, August 18, 2011 10:30 AM
    Wednesday, August 17, 2011 7:35 PM