Solved

Question with SQL DateDiff

Posted on 2011-03-04
10
489 Views
Last Modified: 2012-05-11
Hi all

I have a table in a SQL 2000 DB that I want to get the number of days between 01/01/1995 and the transaction date (this is automatically populated by our system). However when I run the below code it is returning the number of days as 38505 for a date of 18/11/2010, but it should be returning 5801.


select *, DateDiff ((day, 01/01/1995, Transaction_Date)+1) as No_Of_Days
from Transaction

Any help welcome
0
Comment
Question by:LukeFileWalker
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35035547
not sure why need + 1

tr this


select *, DateDiff (day, 01/01/1995, Transaction_Date) as No_Of_Days
from Transaction

then


select *, DateDiff (day, 01/01/1995, Transaction_Date) +1 as No_Of_Days
from Transaction
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35035588
try:-

select *, DateDiff ((day,'01/01/1995', Transaction_Date)+1) as No_Of_Days
from Transaction

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35035599
+1 is so it counts the first day as well. For example, DateDiff (day, 01/01/1995, 02/01/1995) would return 1 (assuming it's dd/mm/yyyy) and he probably wants it to return 2 (day 1 and day 2).
Other than that, pratima hit the nail. You have to place the +1 outside the datediff.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 35035602
you have got 1 divided by 1 divided by 1995 there = 0 which is 01/01/1900 or something

try

DateDiff ((day, CAST('01-01-1995' AS DATETIME), Transaction_Date)+1) as No_Of_Days

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35035604
You need quotes around the date value and the +1 should be outside the datediff function

select *, DateDiff (day, '01/01/1995', Transaction_Date)+1 as No_Of_Days
from Transaction
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35035612
you are missing the single quotes.
0
 
LVL 18

Expert Comment

by:deighton
ID: 35035617
DateDiff (day, CAST('01-01-1995' AS DATETIME), Transaction_Date) + 1 as No_Of_Days
0
 

Author Comment

by:LukeFileWalker
ID: 35035628
Thanks Pratima

the +1 is there as I'm tring to get a date key number and without the +1 it takes it that 01/01/1995 was day zero not day 1, if you get me

Tried that and I'me getting 40498 for the first option and 40499 for the +1 version.

My bad, what I actually ran originally was

select *, DateDiff ((day, 1995/01/01, Transaction_Date)+1) as No_Of_Days
from Transaction

to get the 38505
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35035640
as already all given input

select *, DateDiff ((day, '1995/01/01', Transaction_Date)+1) as No_Of_Days
from Transaction
0
 

Author Closing Comment

by:LukeFileWalker
ID: 35035689
You truly are a steely eyed rocket man.

Many thanks all, catch you on the other side
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question