Solved

Question with SQL DateDiff

Posted on 2011-03-04
10
493 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 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