SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting dates to number of days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  13:28:36  Show Profile  Reply with Quote
HI ,

I need some help to convert the difference in 2 dates into number of days?

example:
1- from date 2/22/14
2- to date 3/6/14

need to take the difference of these 2 and convert it to number of days.
Thanks,
Pasi

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 03/06/2014 :  14:01:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you looked at the DATEDIFF function?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  14:37:35  Show Profile  Reply with Quote
I have but these date field "admit date" and "release dates" are 2 pick list from an application. once picked I need to convert them to a number of days, and I am not sure how to do that>
the DATEDIFF function is looking for some thing like SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

but my 2 date filed dates are called relase date--> "to_routine" and admit date --->"from routine" how do I do it with these fields name?

Thanks.
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/06/2014 :  14:48:47  Show Profile  Reply with Quote
SELECT DATEDIFF(dd,from_routine,to_routine) AS diff

=======================
Not an Expert, Just a learner.
!_(M)_!

Edited by - maunishq on 03/06/2014 14:49:31
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 03/06/2014 :  14:48:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
SELECT DATEDIFF(dd,release_date,admit_date)
FROM yourtable
WHERE ...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 03/06/2014 14:52:37
Go to Top of Page

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  16:18:49  Show Profile  Reply with Quote
Thaks s TKizer. can you alos tell me how to convert a date string like 20140104 to 01/04/2014?
Thanks.
Go to Top of Page

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  16:19:35  Show Profile  Reply with Quote
Thanks!!!
quote:
Originally posted by maunishq

SELECT DATEDIFF(dd,from_routine,to_routine) AS diff

=======================
Not an Expert, Just a learner.
!_(M)_!

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 03/06/2014 :  16:21:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
No need to convert it. SQL Server can handle that format for the DATEDIFF function. If you are asking for presentation purposes, then I'd suggest not doing this conversion in T-SQL. Presentation layer issues should be handled by the application and not by SQL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  16:24:09  Show Profile  Reply with Quote
Well, I am using it in crystal report which uses the SQL db. when I bring this date it does not convert it. I did it once before but cant figure it out for some reason! too much in mind! :)

quote:
Originally posted by tkizer

No need to convert it. SQL Server can handle that format for the DATEDIFF function. If you are asking for presentation purposes, then I'd suggest not doing this conversion in T-SQL. Presentation layer issues should be handled by the application and not by SQL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 03/06/2014 :  16:31:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Crystal Reports should be the one to do the conversion then, not SQL. I'm not a Crystal Reports designer though, have never used it, but I know it can convert dates.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Pasi
Posting Yak Master

150 Posts

Posted - 03/06/2014 :  16:55:10  Show Profile  Reply with Quote
Yah I got to figure it out. thnx.
quote:
Originally posted by tkizer

Crystal Reports should be the one to do the conversion then, not SQL. I'm not a Crystal Reports designer though, have never used it, but I know it can convert dates.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 03/10/2014 :  07:27:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
In Crystal reports there is an option called Format Field which you can make use of.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000