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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Convert nvarchar to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sudpat
Starting Member

1 Posts

Posted - 01/20/2006 :  16:40:42  Show Profile  Reply with Quote
Hi,

I have a nvarchar column's like below:-

first date second date
=========== ============
20060120112128 20060120112659

I need to find the difference in these two dates how can I do that???
should i have to convert these two columns to datetime and then take a date diff?? if so whats the syntax.....

I would really apreciate any help!!!!!

Thanks and Regards.

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 01/20/2006 :  22:15:01  Show Profile  Reply with Quote
format the string first then convert to datetime.
declare @strdate	varchar(20)

select	@strdate 	= '20060120112128'

select convert(datetime, left(@strdate, 8) + ' ' + substring(@strdate, 9, 2) + ':' + substring(@strdate, 11, 2) + ':' + substring(@strdate, 13, 2))


You should use proper data type for datetime

-----------------
'KH'


Edited by - khtan on 01/21/2006 07:19:49
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2006 :  06:22:51  Show Profile  Reply with Quote
khtan: Pls Note: your substring for the Time part is one-off

"I need to find the difference in these two dates"

You can find the number of minutes, or seconds, or whatever between them:

DECLARE	@strFirstDate	varchar(20),
	@strSecondDate	varchar(20)

SELECT	@strFirstDate 	= '20060120112128',
	@strSecondDate	= '20060120112659'

SELECT	CONVERT(datetime, LEFT(@strFirstDate, 8) + ' ' + substring(@strFirstDate, 9, 2) + ':' + substring(@strFirstDate, 11, 2) + ':' + substring(@strFirstDate, 13, 2)),
	CONVERT(datetime, LEFT(@strSecondDate, 8) + ' ' + substring(@strSecondDate, 9, 2) + ':' + substring(@strSecondDate, 11, 2) + ':' + substring(@strSecondDate, 13, 2)),
	DATEDIFF(Minute,	-- Or Hours / Seconds / whatever
		CONVERT(datetime, LEFT(@strFirstDate, 8) + ' ' + substring(@strFirstDate, 9, 2) + ':' + substring(@strFirstDate, 11, 2) + ':' + substring(@strFirstDate, 13, 2)),
		CONVERT(datetime, LEFT(@strSecondDate, 8) + ' ' + substring(@strSecondDate, 9, 2) + ':' + substring(@strSecondDate, 11, 2) + ':' + substring(@strSecondDate, 13, 2))
		)

Kristen
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 01/21/2006 :  07:18:00  Show Profile  Reply with Quote
Oops. Guess I need a new pair of specs

-----------------
'KH'

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 01/21/2006 :  07:24:55  Show Profile  Reply with Quote
or Maybe i am using Kristen's Time

-----------------
'KH'

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2006 :  09:05:21  Show Profile  Reply with Quote
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