none
Insert DATE to database (format 'YYYY-MM-DD')

    Question

  • How can I insert BIRTHDATE into column in 'YYYY-MM-DD' format.

    This is my code, can anyone edit my code? Tnx

    String = "INSERT into concfile VALUES(BIRTHDATE('" & DateTimePicker1.Text & "', 'YYYY-MM-DD'))"

    Friday, August 27, 2010 11:47 AM

Answers

  • I got the Answer from my own:

    This is the code.

    String = "INSERT into concfile VALUES('" & Format(DateTimePicker1.Value, "yyyy-MM-dd") & "')"

     

    Tnx anyway..
    • Marked as answer by MiCzZz Saturday, August 28, 2010 12:22 AM
    Saturday, August 28, 2010 12:22 AM

All replies

  • Hello,

    I suggest to use parameters for queries, then you don't have to take care about date formats.

    You have to convert the texual date into a datetime type value, here you can use the CONVERT function together with the format parameter 120 = ODBC format YYYY-MM-DD. The effective SQL statement should look like the follwing:

    INSERT INTO concfile (BirthDate)

    VALUES (CONVERT(datetime, '2010-08-27', 120))

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, August 27, 2010 11:59 AM
  • How can I insert BIRTHDATE into column in 'YYYY-MM-DD' format.

    This is my code, can anyone edit my code? Tnx

    String = "INSERT into concfile VALUES(BIRTHDATE('" & DateTimePicker1.Text & "', 'YYYY-MM-DD'))"

    I hope you are not using varchar or char data type to strore dates....

    If you are using datetime date type then remember


    SQL Server does not store date or time as a string literal or any string format (I think biggest confusion comes when you use client application including management studio or Enterprise manager to view data) and it shows you date and time as a formatted string literal so make people think that  that is how sql is storing dates.)

    So there is no point saving formatted dates using INSERT Statement....

    Datetime datatype stores dates as two four bytes interger... one integer representing number of days since 1st January 1900 for date
    and second integer represents number of ticks (1/300 of a second) since midgnight for time.

    How Are Dates Stored In SQL Server?

    Ultimate guide to Datetime data types


    If you use ISO 8601 format to pass date from your client app, you should be fine...

    http://msdn.microsoft.com/en-us/library/ms187819.aspx

    Also, you can use convert function to format dates but formatting should be left on the presentation layer ...

    so if you are using crystal reports let crystal reports handle formatting your date in particular format..or your client application is .net then framework has reach set of functions for date formatting...

     

    Friday, August 27, 2010 12:31 PM
  • I am using DATE datatype. The date in my textbox is in this format mm/dd/yyyy. So, I need to insert it in table CONCFILE in this format 'YYYY-MM-DD', to accept it in database. tnx
    Friday, August 27, 2010 11:27 PM
  • In that case, even if you attempt to store it 'YYYY-MM-DD' format, it is not stored internally in the your table in that format

     

    Friday, August 27, 2010 11:50 PM
  • Can you simply use a parameter?

    String = "INSERT into concfile (BIRTHDATE) values (@BirthDate)"
    
    Command.Parameters.AddWithValue("@BirthDate",
    DateTimePicker1.Text)
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Saturday, August 28, 2010 12:10 AM
    Moderator
  • I got the Answer from my own:

    This is the code.

    String = "INSERT into concfile VALUES('" & Format(DateTimePicker1.Value, "yyyy-MM-dd") & "')"

     

    Tnx anyway..
    • Marked as answer by MiCzZz Saturday, August 28, 2010 12:22 AM
    Saturday, August 28, 2010 12:22 AM