Need help on SQL - 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 2005 Forums

Transact-SQL (2005)

Need help on SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-17 : 11:50:23

[code]declare @tTime table
(TTime char(6));
insert into @tTime values('013000');
insert into @tTime values('213000');[/code]

how my SQL look's like to display as follow
[code]myDateTime
2010-06-29 01:30:00.000
2010-06-29 21:30:00.000[/code]

Let's say 2010-06-29 is getdate()

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-17 : 12:10:53

[code]
select cast(convert(varchar(10),getdate(),120) + ' ' +
left(TTime,2) + ':' + substring(TTime,3,2) + ':' + right(TTime,2) as datetime)
from @tTime
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 03:21:42

select 
	dateadd(day,datediff(day,0,getdate()),0)+stuff(stuff(TTime,3,0,':'),6,0,':') 
from @tTime

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 03:26:03

quote:

Originally posted by RickD


select cast(convert(varchar(10),getdate(),120) + ' ' +
	left(TTime,2) + ':' + substring(TTime,3,2) + ':' + right(TTime,2) as datetime)
from @tTime

Better use style 112 that interprets dates without hyphen. Otherwise it would be ambiguous and may lead to unexpected result or error
Also read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-18 : 04:13:05

Possibly Madhi, but the OPs results had hyphens.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 05:10:08

quote:
Originally posted by RickD

Possibly Madhi, but the OPs results had hyphens.

Yes. I didn't speicify about the result. Both yours and mine resturn same result.But When you use style 120 that had hyphen with time part without Time seperator, and cast it to datetime,it may lead to error or unexpected result depending upon the date settings of the server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources