Solved

Question with SQL DateDiff

Posted on 2011-03-04
497 Views
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
Question by:LukeFileWalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +3

LVL 39

Expert Comment

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

ID: 35035588
try:-

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

LVL 18

Expert Comment

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

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

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

ID: 35035612
you are missing the single quotes.
0

LVL 18

Expert Comment

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

Author Comment

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

ID: 35035640

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

Author Closing Comment

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

Many thanks all, catch you on the other side
0

Featured Post

Question has a verified solution.

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

Suggested Solutions

Help with Data Warehouse / Data Marts 4 42
Use SSRS to email customers? 4 29
MAC Dreamweaver connect to external MS SQL Server 2 39
relocating SQL 2000 18 36
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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month4 days, 16 hours left to enroll