Convert nvarchar to datetime - SQL Server Forums

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)

Convert nvarchar to datetime

Author

Topic

sudpat
Starting Member

1 Post

Posted - 2006-01-20 : 16:40:42

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)

17689 Posts

Posted - 2006-01-20 : 22:15:01

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'

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 06:22:51

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-21 : 07:18:00

Oops. Guess I need a new pair of specs