locked
Insert Date/Time Question... RRS feed

  • Question

  • Hello,

    I have an access database that contans a field called 'My_Date'.  Since I want the column to store the date and time, I made the datatype of the the column 'Date/Time'. 

    When I just insert the date like this:

    Insert Into Meeting (My_Date) Values ('01/01/01')

    The default time of '12:00:00AM' is given. 
    So when i query the field I get the following output:

    01/01/01 12:00:00AM

    How can i make it so when i insert a date and no time, there is no default value of 12:00:00AM?  So when i query i will just get:

    01/01/01

    Thanks,

    Thursday, July 27, 2006 8:13 PM

Answers

  • Access always stores date and time in the datetime field.

    So if you avoid sending the time it just uses 12:00:00 AM.

    You can format the datetime field to hide the time using format function, like so:

     select format(f1,"yyyy-mm-dd") as x from Table1

    etc...

    Friday, July 28, 2006 10:52 PM

All replies

  • Try changing the Format of your Date/Time column to 'Short Date' in Access.

    Thursday, July 27, 2006 8:59 PM
  • I just changed the Format to Short Date, but unfortunately it didn't help.

    I just tested it with the following insert command:

    Insert Into Meeting (My_Date) Values ('24/01/2006')

    When I query this field I get the following still:

    24/01/2006 12:00:00AM

    Friday, July 28, 2006 12:55 AM
  • Access always stores date and time in the datetime field.

    So if you avoid sending the time it just uses 12:00:00 AM.

    You can format the datetime field to hide the time using format function, like so:

     select format(f1,"yyyy-mm-dd") as x from Table1

    etc...

    Friday, July 28, 2006 10:52 PM
  • Hmm.. okay.  Thanks for clearing that up.  It looks like access won't be able to fill my needs then :P.  You pretty much have to choose whether you want to display the date and time or not.  You are unable to sometimes display the data and time and other times just the date.
    Saturday, July 29, 2006 3:22 AM
  • Another way to handle this is add another field to the table called DateMode where:

    1 = Display date only, 2=display time only, 3 = display both date and time

    Then you could create a query that calculated the display output like so:

    select
    choose([DateMode],Format([DateValue],"yyyy-mm-dd"),Format([DateValue],"hh:nn:ss"),Format([DateValue],"yyyy-mm-dd hh:nn:ss")) as DateDisplay,
    DateValue,
    DateMode
    FROM DateTest

    The choose function is like a switch statement, it uses first param value 1,2,3,4... and then uses the expression from param 2,3,4... as output.

    Another alternative is to store datetime in textual field but this is usually troublesome to handle because sorting on textual format of date does not work in same fashion as real datetime field so I don't recommend it.

     

     

    Sunday, July 30, 2006 6:34 PM