How to get number of days between 2 MMDDYYYY

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 2008 Forums

Transact-SQL (2008)

How to get number of days between 2 MMDDYYYY

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-04-14 : 12:01:45

Hi all,
I am having a flat file and it is having 2 columns called
BeginDate as varchar(10) and EndDate as varchar(10) in the form of MMDDYYYY.

Now i need to write a formula to get number of days between those 2 days.

Number of Days = (EndDate - BeginDate)

Ex:
if (Enddate = 06202008 and Begindate = 06192008)
then
Noofdays = 1 day.

How can i Achieve this?

-Thanks N Regards,
Chinna.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-14 : 12:26:42

Import it into a table. Use datediff function to find out the number of days. Use converted enddate,begindate to DATETIME while using datediff.

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-15 : 02:25:23

Hi,
DateDiff is best way and the other way of doing is

DECLARE @Begindate VARCHAR(10), @Enddate VARCHAR(10)
SET @Enddate = '06202008'
SET @Begindate = '06192008'
SELECT CAST(SUBSTRING(@Enddate,3,2) AS INT) - CAST(SUBSTRING(@Begindate,3,2) AS INT)

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 02:27:52

Rajesh, try with these dates

DECLARE @Begindate VARCHAR(10), @Enddate VARCHAR(10)
SET @Enddate = '07202008'
SET @Begindate = '06192008'

SELECT CAST(SUBSTRING(@Enddate,3,2) AS INT) - CAST(SUBSTRING(@Begindate,3,2) AS INT)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 02:29:54

[code]DECLARE @Begindate VARCHAR(10),
@Enddate VARCHAR(10)

SELECT @Begindate = '06192008',
@Enddate = '07202008'

SELECT DATEDIFF(DAY,
CONVERT(DATETIME, STUFF(STUFF(@BeginDate, 3, 0, '/'), 6, 0, '/'), 101),
CONVERT(DATETIME, STUFF(STUFF(@EndDate, 3, 0, '/'), 6, 0, '/'), 101)
)[/code]

E 12°55'05.63"
N 56°04'39.26"

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-15 : 02:36:22

Hi Pesco,

You are right
My query works only for particular month.

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Kokkula
Starting Member

41 Posts

Posted - 2009-04-15 : 02:39:44

Hello,

Try this

DECLARE @Begindate VARCHAR(10), @Enddate VARCHAR(10)
SET @Enddate = '07202008'
SET @Begindate = '06192008'

SET @Begindate = SUBSTRING(@Begindate,1,2) + '-' + SUBSTRING(@Begindate,3,2) + '-' + SUBSTRING(@Begindate,5,4)
SET @Enddate = SUBSTRING(@Enddate,1,2) + '-' + SUBSTRING(@Enddate,3,2) + '-' + SUBSTRING(@Enddate,5,4)

SELECT DATEDIFF(Day,@Begindate,@Enddate)

Hope helpfull...

Thanks,
Pavan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources