locked
How to insert a value in a date/time field RRS feed

  • Question

  • I want to insert a timestamp in a field of the type date/time.

    How do I format my datavalue to be independent of the Windows regional settings for date-format?

    I want the routine to work - no matter how Windows is set op on the specific PC. Is that possible?

    Monday, November 19, 2012 3:51 PM

Answers

  • What if 5th november 2007 is written: 05-11-2007 or even worse 05-11-07

    If you are referring to the value being entered into a column (field) of date/time data type in Access then any of those will be stored as the same underlying value on a system set up to use the UK date format.  If you then put the table onto a system using US date format it will still be the same date in Access, but will be shown in US date format.

    If you need to convert an Access date/time value to a string expression the ISO Standard for Date/Time Notation is YYYY-MM-DD hh:mm:ss.  The standard uses case to differentiate between months and minutes, whereas Access uses m and n respectively, so if you format an Access date/time value with Format([TheDateTimeField],"yyyy-mm-dd hh:nn:ss") regardless of the regional date time format in use, the resulting string will be correct.  As I type here in the UK:

    ? Now(), Format(Now(),"yyyy-mm-dd hh:nn:ss")
    21/11/2012 21:39:57         2012-11-21 21:39:57
    ? CDate("2012-11-21 21:39:57")
    21/11/2012 21:39:57

    If I were over the pond it would I'd get:

    ? Now(), Format(Now(),"yyyy-mm-dd hh:nn:ss")
    11/21/2012 21:39:57         2012-11-21 21:39:57
    ? CDate("2012-11-21 21:39:57")
    11/21/2012 21:39:57

    The underlying value in each case is:
    ? CDec(#2012-11-21 21:39:57#)
     41234.9027430556

    which simply means it's 41234.9027430556 days since 30 December 1899 00:00:00

    BTW, if you want a simple example of how to time-stamp records take a look at the demo file ChangeRecordDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169



    Ken Sheridan, Stafford, England

    • Marked as answer by Dummy yoyo Monday, December 10, 2012 4:55 AM
    Wednesday, November 21, 2012 9:56 PM

All replies

  • You can set the value of the field to Now() - this returns the current date+time. Or if you want to store only the date, set the value to Date() - this returns the current date only.

    For example, if you want to populate a field LastUpdated whenever a record in the record source of a form is updated, you can use the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Me.LastUpdated = Now
    End Sub


    Regards, Hans Vogelaar

    Monday, November 19, 2012 4:32 PM
  • You want the current timestamp? Use the Now() function.

    You want an arbitrary timestamp? Add together the results of using the DateSerial and TimeSerial functions.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, November 19, 2012 4:33 PM
  • How do I format my datavalue to be independent of the Windows regional settings for date-format?

    I want the routine to work - no matter how Windows is set op on the specific PC. Is that possible?

    Whatever you enter must be translated into a format that Access will recognize as a date.

    Are you want to either enter USA format or EU format and Access go with it?

    Monday, November 19, 2012 4:42 PM
  • Sorry - I was not specific...

    I get the time-info from a PLC in form of untegers for: Year, month, date, hour, minute and seconds

    This I have to combine into a valid date/time value. My problem is, that the format of this is linked to the regional setting of the host-PC.

    Like this:

    ' based on English(UK) time format in Windows 7 - make sure it matches the operating system on the PC
    DateTime1 = IIf(Len(CStr(year)) = 2, "20", "") & year & "-" & month & "-" & day & " " & hour & ":" & minute & ":" & second

    The use of "-" as delimiter is related to the regional setting.

    Is there somehwo a way to use a general format that is independant of the regional settings?

    Tuesday, November 20, 2012 10:30 AM
  • Hi DianePDavies,

    create your datetime1 n this way

    datetime1=Year([yourdate]) & Right("00" & Month([yourdate]),2) & Right("00" & Day([yourdate]),2) & Right("00" & Hour([yourdate]),2) & Right("00" & Minute([yourdate]),2)

    replace yourdate with the date and the time that you wanna make indipendant from the regional settings and in datetime1 you'll have a string that respect the sort order of a date and you can easily reconvert to a true date.

    HTH Paolo

    Tuesday, November 20, 2012 10:58 AM
  • Be aware that date/time values are stored as eight-byte floating point numbers where the integer portion represents the date as the number of days relative to 30 Dec, 1899, and the decimal portion represents the time as a fraction of a day. In other words, date/time values are NOT stored in any particular format.

    You're best off using the DateSerial and TimeSerial functions, as I suggested previously:

    DateTime1 = DateSerial(IIf(Len(CStr(year)) = 2, 2000, 0) + year, month, day) + TimeSerial(hour, minute, second)


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Tuesday, November 20, 2012 11:31 AM
  • OK - my test shows that DateSerial and TimeSerial will format the data as defined by the Regional Settings in Windows.

    But still if I format my timestamp on one PC1 and communicates it to another PC2 - then I have no guarantee that it will be understood correctly.

    But if the data is transmitted as year, month, date, hour, minute and second - then DateSerial and TimeSerial will do it if used on PC2.

    I would however like to format a timestamp as a textstring on PC1 and then have it understood by the PC2... Can I make a textstring in the form of a decimal number with the integer being the days since 30 Dec 1899 etc.? And how do I easily get "the days since 30 Dec 1899 "?

    Tuesday, November 20, 2012 3:09 PM
  • Did you read my suggestion?

    If you use my suggestion you'll get exactly what you wanna achieve i.e. a sting containing yyyymmddhhmm that you can transfer to yor PC2 as a string without misunderstanding.

    Regards

    Tuesday, November 20, 2012 3:29 PM
  • ...I did - but I have the date and time info in the form of year, month, date, hour, minute and second  - and not as a  single datevalue... so at first I just skipped it. Sorry.

    Now I used your concept and now formats that date as eg. "2012112016041"

    But when I want to insert this value I get a type conversion error. I belive that is why I originally had to format it according to the Regional Settings.

    I just tried inserting 25000.25

    That actually works out - and I get the date: 11/06/1968 06:00:00

    So I guess I have to format my tima in relation to 1899.

    Tuesday, November 20, 2012 4:24 PM
  • Now I used your concept and now formats that date as eg. "2012112016041"

    Access does not recognize that number as a datetime.  Use the DateSerial as Douglas posted.
    Tuesday, November 20, 2012 5:19 PM
  • Reread my message. Date/Time values are stored without formatting. The same date/time value will display in whatever format is set for the PC.

    Perhaps Allen Browne's International Dates in Access will help you understand.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Wednesday, November 21, 2012 12:38 AM
  • Hi Diane.

    There may be a misunderstanding in this thread…

    A Date/Time field in an Access (Jet) table is a floating point Double numeric data type. Well actually, since it can be Null, it is a Variant but let’s skip that for the moment and think about the Double.

    The number held in the Double represents a number of days. As a number of days it is not a date as such but a duration of time. It only becomes a date when Access adds that duration of time to some base date, epoch 0.

    In Access, epoch 0 is defined as midnight 30-Dec-1899 where midnight is defined as the first instance of the new day. Hence, in Access, epoch 0 is AM not PM.

    The Date/Time (double) field in an Access (Jet) has a value of 0 for epoch 0.
    1.
    Unfortunately Access displays epoch 0, the double value of 0, as 12:00:00 AM.
    Dim datSomeDate As Date
    datSomeDate = 0
    MsgBox datSomeDate
    I think that is unfortunate in that we only see the time and not the date.

    2.
    If we add a value of 1 to the epoch 0 Date/Time (double) field we get 31/12/1899:-
    Dim datSomeDate As Date
    datSomeDate = 1
    MsgBox datSomeDate
    I think that is unfortunate in that we only see the date and not the time.
    What we do see, though, is the addition of 1 to epoch 0 and so we see 1 day added to the 30/12/1899 which then becomes 31/12/1899.

    3.
    If we add a value of 1.25 to the epoch 0 Date/Time (double) field we get 31/12/1899 6:00:00 AM:-
    Dim datSomeDate As Date
    datSomeDate = 1.25
    MsgBox datSomeDate
    Now we have a display of both the date and the time.


    I think what we should note from the above is this…
    a.
    The data which is stored in an Access (Jet) table Date/Time field is a Double and that is all there is to it. It has nothing to do with the display of that data.

    b.
    When Access needs to display that data it adds it to epoch 0 which is midnight 30-Dec-1899.

    c.
    The date of epoch 0 is not stored in the table, only the offset (duration of time) in a number of days is stored.

    d.
    The display of the data, after adding it to epoch 0, is done under the influence of regional settings when it becomes a date/time for display.

    e.
    Access will not display a value for a date of 0 or a time of 0 unless both are 0 when it will display 12:00:00 AM.

    f.
    Two distinct applications of a date/time apply:-
    i. Direct application in code, which includes SQL strings...
    Remove all possible interference due to regional settings.

    ii. Direct display to the end user…
    Apply regional settings to the displayed date/time.


    So, from the data you have presented so far, use the method as recommended by Doug to calculate and store the date and time. Pass that stored value as a Date/Time value or as a Double value. Allow the receiving PC to handle it as above.

    If, for some reason, you do have to pass the value of a Date/Time field as a string do the following…

    Dim datSomeDate       As Date
    Dim strSomeString     As String
    Dim dblReceivedDouble As Double

    datSomeDate = 1.25
    strSomeString = Str(CDbl(datSomeDate))

    ' Pass the string and extract the received value.
    dblReceivedDouble = Val(strSomeString)
    MsgBox dblReceivedDouble

    Note in the above code…
    a. CDbl(datSomeDate)
    The CDbl() function bypasses Access trying to format the datSomeDate as a date/time and returns the actual value of datSomeDate as a Double.

    b.
    The Str() function converts the datSomeDate as a Double to a string.
    It is important to note that we use the Str() function here and not the CStr() function.
    The data converted to a string is a Double and that Double will contain a decimal separator. In regional settings the decimal separator may be a comma, and not a period, as in German for instance.

    The CStr() function will produce a string based on regional settings and so could include a comma as the decimal separator. A subsequent Val() function would then truncate the passed string at the comma. That situation would effectively remove any time portion of the passed string and only return the date portion.

    On the other hand, the Str() function works correctly because it is too old to understand regional settings. (Chalk up one for the old. <grin>)  The Str() function only returns a period as the decimal separator and so is perfectly acceptable in any subsequent Val() function under any regional settings.

    Once the receiver has the correct Double value it can be processed as a Date/Time in any way the coder sees fit.

    Chris.

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive


    Wednesday, November 21, 2012 3:24 AM
  • you have yyyymmddhhmmss as a text sting and in that way you can send it from PC1 to PC2 with different regional settings without misunderstanding between the two pc. A string is a string.

    To convert it in a date/time on both pc use dateserial and timeserial as suggested by others passing to the function the correct pieces of the string.

    datefromstring=dateserial(cint(left(yyyymmddhhmmss,4)),cint(mid(yyyymmddhhmmss,5,2)),cint(mid(yyyymmddhhmmss,7,2)))

    timefromstring=timeserial(cint(mid(yyyymmddhhmmss,9,2)),cint(mid(yyyymmddhhmmss,11,2)),cint(mid(yyymmddhhmmss13,2)))

    in this way every pc generate the date and the time with its own regional settings.

    yyymmddhhmmss= IIf(Len(CStr(year)) = 2, "20", "") & year & right("00" & month,2) & right("00" & day,2) & right("00" & hour,2) & right("00" & minute,2) & right("00" & second,2)

    Regards Paolo

    Wednesday, November 21, 2012 7:50 AM
  • This is exactly my conclusion.

    First I wanted to communicate a text file from PC1 to PC2 and then import the data into Access without further handling. The problem is though that if I from PC1 use dateserial and timeserial to generate a string representing the date in this text file - then this will be in a format that reflects the regional settings - and this may not be understood on PC2. As a simple "import" does not interprete the string representing the date it fails if the regional settings differ on PC2.

    So I was looking for a solution where I could communicate a text-string representing the date in a format that could be imported irregardless of the regional settings on PC2.

    This however is too complicated - in the sense that nobody would understand a double showing a value in relation to midnight 30 December 1899. So to keep things simple I shall communicate the date in the format yyyymmddhhmmss and then on PC2 use dateserial and timeserial to insert the data in a proper date/time field. By communicating data like this I can however not just do a simple "import" - but will have to transform my yyyymmddhhmmss string afterwards. Unless there is some way of specifying use of dateserial and timeserial in the import function.

    Anyway - this has been a valuable exercise - as I over the years have had issues with the date/time data type where I did not understand how to insert values... The functions timeserial and dateserial clearly does this right - no matter the regional settings.

    Wednesday, November 21, 2012 9:52 AM
  • You'd be far better off communicating the date/time values as yyyy-mm-dd hh:nn:ss. In that way, you can use the built-in CDate function to convert it to a proper date time.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Wednesday, November 21, 2012 2:27 PM
  • Using Cdate seems clever.

    Cdata can handle "Any expression that can be interpreted as a date". Am I then absolutely sure that there is no confusion due to regional settings?

    What if 5th november 2007 is written: 05-11-2007 or even worse 05-11-07

    If I use the format yyyy-mm-dd will it then assume that mm is in fact the month and not the date?

    Since DateSerial through the parameters knows exactly what is what I would tend to prefer that one. 

    Wednesday, November 21, 2012 2:48 PM
  • yyyy-mm-dd is an international standard. If for some perverse reason, you wanted the date to be represented as yyyy-dd-mm, you'd have to go through extra hoops.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Wednesday, November 21, 2012 3:19 PM
  • What if 5th november 2007 is written: 05-11-2007 or even worse 05-11-07

    If you are referring to the value being entered into a column (field) of date/time data type in Access then any of those will be stored as the same underlying value on a system set up to use the UK date format.  If you then put the table onto a system using US date format it will still be the same date in Access, but will be shown in US date format.

    If you need to convert an Access date/time value to a string expression the ISO Standard for Date/Time Notation is YYYY-MM-DD hh:mm:ss.  The standard uses case to differentiate between months and minutes, whereas Access uses m and n respectively, so if you format an Access date/time value with Format([TheDateTimeField],"yyyy-mm-dd hh:nn:ss") regardless of the regional date time format in use, the resulting string will be correct.  As I type here in the UK:

    ? Now(), Format(Now(),"yyyy-mm-dd hh:nn:ss")
    21/11/2012 21:39:57         2012-11-21 21:39:57
    ? CDate("2012-11-21 21:39:57")
    21/11/2012 21:39:57

    If I were over the pond it would I'd get:

    ? Now(), Format(Now(),"yyyy-mm-dd hh:nn:ss")
    11/21/2012 21:39:57         2012-11-21 21:39:57
    ? CDate("2012-11-21 21:39:57")
    11/21/2012 21:39:57

    The underlying value in each case is:
    ? CDec(#2012-11-21 21:39:57#)
     41234.9027430556

    which simply means it's 41234.9027430556 days since 30 December 1899 00:00:00

    BTW, if you want a simple example of how to time-stamp records take a look at the demo file ChangeRecordDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169



    Ken Sheridan, Stafford, England

    • Marked as answer by Dummy yoyo Monday, December 10, 2012 4:55 AM
    Wednesday, November 21, 2012 9:56 PM
  •  Using Ken’s number to illustrate the equivalence of Dates and Doubles and to show he should be paid more for overtime :)

    Sub TestIt()
        Dim dblSomeDouble As Double
        
        dblSomeDouble = 41234.9027430556
    
        MsgBox CDate(dblSomeDouble)
        
        MsgBox Format(dblSomeDouble, "yyyy-mm-dd hh:nn:ss")
    
    End Sub
    

    So the Date/Double is totally unambiguous as far as Access is concerned; it works everywhere.

    But that Date/Double can be formatted in any way so that it becomes fit for human consumption.

    Chris.


    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

    Wednesday, November 21, 2012 10:57 PM
  • By the same token, you can use

    Sub TestIt()
    Dim dtmSomeDate As Date
        
      dtmSomeDate = #2012-11-21 21:39:57#
      MsgBox Format(dtmSomeDate, "#.#######")
    End Sub


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)


    Thursday, November 22, 2012 1:48 AM
  • Actually, Doug, we can’t use that; it becomes an unbalanced red 24 legged octothorpe. :)

    But it does reinforce the equivalence of the Date/Time and the Double data types.

    Cheers,
    Chris.

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

    Thursday, November 22, 2012 2:06 AM
  • On further testing we would also need to expand the number of decimal places to 10 in order to get back to Ken’s number of 41234.9027430556.

    So:-

    Sub TestIt()
    Dim dtmSomeDate As Date
    
      dtmSomeDate = #11/21/2012 9:39:57 PM#
      MsgBox Format(dtmSomeDate, "#.##########")
    
    End Sub
    

    Then we would have an exact equivalence.

    Chris.

     

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

    Thursday, November 22, 2012 2:33 AM