Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to compare 2 dates in SQL Server 2000?

Author  Topic 

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 06:20:51
How do you compare 2 dates in SQL Server 2000 DB?
Let's say we have two fields: Date Reported and Target Finish Date.

Date Reported is 09-10-2004 09:55:55: PM
Target Finish Date is 09-12-2004 11:59:59: PM

What i want to happen is i want to convert both dates to days and get the difference of the two.
can SQL Server 2000 DB do this?
What is the query for this?

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 06:23:44
will this help?

select day(TargetFinishDate) - day(DateReported)

Go with the flow & have fun! Else fight the flow
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2004-09-10 : 06:27:33
Look up the datediff command in books online.

-------
Moo. :)
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 06:28:37
no that won't help. i want SQL Server to automatically computer the difference of the 2 dates.
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 06:29:56
mr_mist: do u have links? i keep on researching but i cant find the answer. it is giving me headache. i cant take this anymore thats y im asking for help in this forum.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-09-10 : 07:02:30
SELECT DATEDIFF(d, FromDate, Todate)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 08:27:25
Lumbago: What is that d in SELECT DATEDIFF(d, FromDate, Todate)
?
what will be the output? will it be string or int?
and how do u get the value (output)?

int days = 0;
		String query = "SELECT DATEDIFF(d, from, to), FROM tblDate";


try
		{   
			DBConnect db = new DBConnect(); 
			db.openCon();
			ResultSet rs = db.execute(query);
			
			while(rs.next())
			{  
				days = rs.getInt("HOW WILL I GET THE OUTPUT HERE? WHAT IS THE CODE?"); 
			}
			db.closeCon();					
		}			
		catch (Exception ex)
		{
			System.out.println("Error on Execution: " + ex);
		}


so u mean that query can compute for the difference of days of the two dates having the format 09-10-2004 09:55:55: PM?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-09-10 : 08:32:08
d stands for day, and you can use any of these:

Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

The datatype returned is integer and you could do like this:
try
		{   
			DBConnect db = new DBConnect(); 
			db.openCon();
			ResultSet rs = db.execute("SELECT DATEDIFF(d, FromDate, Todate) AS Diff");
			
			while(rs.next())
			{  
				days = rs.getInt("Diff"); 
			}
			db.closeCon();					
		}			
		catch (Exception ex)
		{
			System.out.println("Error on Execution: " + ex);
		}
Not 100% sure on the C# but hopefully you'll get the idea...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 08:39:21
it's not wrking, an error nullpointerexception had occurred.
yah iv seen the logic but i think the code should read the format of the date. so what is the format of the date that that code reads?

let's say my "from date": 08-10-2004 09:55:55: PM
and my "to date": 09-12-2004 09:55:55: PM

the output must be 33.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-09-10 : 08:42:16
Both dates need to be in "datetime"-format in the database. If they are not then you have to do some conversion.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 08:48:01
what is the format of the datetime datatype?
is it 10/22/04 or 10-22-04 or what?

u mean i need to make the datatype of my the columns From Date and To Date datetime?

or il just have to convert the format of the date 10-22-2004 to the same format of the datetime datatype?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 08:56:18
dates are stored as bigint's counting ticks from some date (can't remember which)

if your columns are not of datetime type use:
select datediff(d, convert(datetime, From), convert(datetime,To))

Go with the flow & have fun! Else fight the flow
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 09:00:48
iv tested a simple code for this case.
I created a table name tblDate which has 3 columns namely date_ID, date_From, date_To.
I inserted 1 row: date_ID has the value 1, date_From has the value 10/22/2004, and date_To has the value 10/24/2004.

i run the java code below:


int days = 0;
		String query = "SELECT DATEDIFF(dd, date_From, date_To AS Diff) FROM tblDate WHERE date_ID = '1'";

			
		try
		{   
			DBConnect db = new DBConnect(); 
			db.openCon();
			ResultSet rs = db.execute(query);
			
			while(rs.next())
			{  
				days = rs.getInt("Diff"); 
			}
			db.closeCon();					
		}			
		catch (Exception ex)
		{
			System.out.println("Error on Execution: " + ex);
		}
	
		return days;


__________________________-
after running, there's an error, Error on Execution: java.lang.nullpointerexception.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-09-10 : 09:00:58
quote:
dates are stored as bigint's counting ticks from some date
Not exactly. SQL Server actually uses two 4-byte integer values. The first stores the number of days from January 1, 1900 (0 = Jan 1 1900), the 2nd integer stores the number of milliseconds since midnight. However, the resolution is only good to 3 milliseconds.

There was going to be a true bigint UTC datetime data type in SQL Server 2005, but it has been dropped. However, you can create CLR datatypes as UDT's and they can provide the same functionality.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 09:08:18
quote:
Originally posted by robvolk

quote:
dates are stored as bigint's counting ticks from some date
Not exactly. SQL Server actually uses two 4-byte integer values. The first stores the number of days from January 1, 1900 (0 = Jan 1 1900), the 2nd integer stores the number of milliseconds since midnight. However, the resolution is only good to 3 milliseconds.

There was going to be a true bigint UTC datetime data type in SQL Server 2005, but it has been dropped. However, you can create CLR datatypes as UDT's and they can provide the same functionality.


really? i read somewhere that it was a bigint. guess i read wrong :)
but it's nice to know, although i have no idea where that can be useful...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 09:09:59
a little modification. try it with that:


String query = "SELECT DATEDIFF(dd, date_From, date_To) AS Diff
 FROM tblDate WHERE date_ID = '1'";


Go with the flow & have fun! Else fight the flow
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 09:16:32
yahooo........ it works. thanks spirit1. thanks to all who helped me. thank you so much! God Bless you all! :)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 09:20:51
now that's a happy man...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

jonasdavedomingo
Starting Member

33 Posts
Posted - 2004-09-10 : 09:37:29
hahaha ur funny mahn, u always follow ur msg with "Go with the flow & have fun! Else fight the flow"

thanks mahn!!! il have a rest now.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-10 : 09:46:39
"Go with the flow & have fun! Else fight the flow" is a signature that u can set in your profile.
there is no way i would be typing that over and over again . it inserts automaticaly.

Go with the flow & have fun! Else fight the flow
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site