?
Solved

Question with SQL DateDiff

Posted on 2011-03-04
10
Medium Priority
?
511 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 18

Accepted Solution

by:
deighton earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 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