If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combining Date/Time
Currently I have a date and a time field in my table. I need to be able to
combine these two items into one field, but when I try to combine them it is subtracting 2 days off of the actual date. My table is in SQL. Is there any way around this, this is the code that I am using update dbo.purgedrecords Set [datetime] = AuthDate + convert(datetime, AuthTime) Previously this was working when the default date on the time was 01/01/1900 and then somehow something changed and the default date is now showing as 12/30/1899. Is there different code that I can use to keep this from happening in the future? |
#2
|
|||
|
|||
Combining Date/Time
The default, zero date is 12/30/1899. Time vales are on the decimal side, so
you are string to add: 0.25 to your current date, and the value is being read as: 12/30/1899 6:00 AM Try converting both values to a string and concatenating the time to the date, then converting back to a date. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Welthey" wrote in message ... Currently I have a date and a time field in my table. I need to be able to combine these two items into one field, but when I try to combine them it is subtracting 2 days off of the actual date. My table is in SQL. Is there any way around this, this is the code that I am using update dbo.purgedrecords Set [datetime] = AuthDate + convert(datetime, AuthTime) Previously this was working when the default date on the time was 01/01/1900 and then somehow something changed and the default date is now showing as 12/30/1899. Is there different code that I can use to keep this from happening in the future? |
#3
|
|||
|
|||
Combining Date/Time
Welthey,
Try just AuthDate + AuthTime. 1/1/08 = 39448.00000 12:00 PM = .5000000 ------------------------------- 39448.500000 = 1/1/08 12:00 PM -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Welthey" wrote in message ... Currently I have a date and a time field in my table. I need to be able to combine these two items into one field, but when I try to combine them it is subtracting 2 days off of the actual date. My table is in SQL. Is there any way around this, this is the code that I am using update dbo.purgedrecords Set [datetime] = AuthDate + convert(datetime, AuthTime) Previously this was working when the default date on the time was 01/01/1900 and then somehow something changed and the default date is now showing as 12/30/1899. Is there different code that I can use to keep this from happening in the future? |
#4
|
|||
|
|||
Combining Date/Time
Those dates you quote (01/01/1900 and 12/30/1899) make it sound as though no
value is actually getting assigned to AuthDate (1 Jan, 1900 is the "base date" in SQL server, while 30 Dec, 1899 is it in Access) That having been said though, I don't believe it's necessary to use the Convert function. Dates and times are stored as numeric values: I believe you can simply add AuthDate + AuthTime. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Welthey" wrote in message ... Currently I have a date and a time field in my table. I need to be able to combine these two items into one field, but when I try to combine them it is subtracting 2 days off of the actual date. My table is in SQL. Is there any way around this, this is the code that I am using update dbo.purgedrecords Set [datetime] = AuthDate + convert(datetime, AuthTime) Previously this was working when the default date on the time was 01/01/1900 and then somehow something changed and the default date is now showing as 12/30/1899. Is there different code that I can use to keep this from happening in the future? |
Thread Tools | |
Display Modes | |
|
|