Solved

Question with SQL DateDiff

Posted on 2011-03-04
10
478 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 115
sql server query? 6 26
How much extra disk space I need for sql backup? 7 46
SQL Help - 12 39
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now