Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrdatabase
Starting Member
12 Posts |
 Posted - 2012-04-28 : 06:28:53
|
| Hi everybody, i am using Microsoft Access to write my SQL code, i was wondering, i have two columns, both date datatypes. How do i subtract the dates from eachother and create a new column within the query to display the results (amount of days different)?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-04-28 : 07:10:21
|
DATEDIFF("DAY", Date1, Date2)
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
mrdatabase
Starting Member
12 Posts |
Posted - 2012-04-28 : 07:17:37
|
| Hi thanks for your reply i have tried this code and it is producing the value "#Func!" For all fields. Would you know why this has happend? and the data types for both columns is datetime, date respectivley. Does that create a problem? Thanks |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-28 : 07:57:36
|
quote:
Originally posted by mrdatabase
Hi thanks for your reply i have tried this code and it is producing the value "#Func!" For all fields. Would you know why this has happend? and the data types for both columns is datetime, date respectivley. Does that create a problem? Thanks
The difference in the data types won't be a problem in your case. The query provided by Sweposo works perfectly in SQL. Infact all the following queries work fine:Declare @date1 date = '20120427'Declare @date2 date = '20120425'Select DATEDIFF(DD, @date2, @date1)----------------------------------------------------------------------Declare @date1 datetime = '20120427'Declare @date2 date = '20120425'Select DATEDIFF(DD, @date2, @date1)----------------------------------------------------------------------Declare @date1 date = '20120427'Declare @date2 datetime = '20120425'Select DATEDIFF(DD, @date2, @date1)----------------------------------------------------------------------Declare @date1 datetime = '20120427'Declare @date2 datetime = '20120425'Select DATEDIFF(DD, @date2, @date1) The problem could be at what you are doing in Access. For thet you need to throw a little more light on whatever you are doing.N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
mrdatabase
Starting Member
12 Posts |
Posted - 2012-04-28 : 08:10:43
|
| Managed to solve the problem, i put d instead of day and it worked! thanks very much for your help :D |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-30 : 00:27:29
|
quote:
Originally posted by mrdatabase
Managed to solve the problem, i put d instead of day and it worked! thanks very much for your help :D
You're welcome...always happy to learn and happier to help.  N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
|
|
|
|
|
Subtracting A Date from another Date