convert 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

Transact-SQL (2000)

convert datetime

Author

Topic

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2006-11-07 : 23:06:43

Hi

how to convert @a=11:28:58 pm to varchar and i have to remove this 'pm'
next thing is i have to add @a=11:28:58 to @B=10:50:20

please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 00:14:37

SELECT CONVERT(varchar, @a, 108)

SELECT CONVERT(varchar, DATEADD(second, DATEDIFF(second, 0, @a) % 86400 + DATEDIFF(second, 0, @b) % 86400, 0), 108)

Peter Larsson
Helsingborg, Sweden

Masum7
Starting Member

33 Posts

Posted - 2006-11-08 : 03:22:11

1.
Peso.. he wants to remove 'pm'..
so write query will b like this:

SELECT convert(varchar(12),CONVERT(datetime, @a),108)

this query not only removes pm but also makes it 24 hour time..

2.the solution of 2nd problem of peso works.. but i have a question.. is there any need to use "%86400"

----- Masum, CSE, BUET

quote:
Originally posted by Peso

SELECT CONVERT(varchar, @a, 108)

SELECT CONVERT(varchar, DATEADD(second, DATEDIFF(second, 0, @a) % 86400 + DATEDIFF(second, 0, @b) % 86400, 0), 108)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 03:33:54

Yes, because DATEDIFF calculates the difference in seconds since 19000101.
There is a possibility of an overflow when adding the large dates together at seconds level.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 04:01:48

A nice workaround without the need for modulo could be

declare	@a datetime,
	@b datetime
select	@a = '20061107 19:32:28',
	@b = '20050113 06:37:51'

select		@a,
		@b

SELECT	CONVERT(varchar, DATEADD(second, SUM(DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, dt), 0), dt)), 0), 108)
FROM	(select @a dt union all select @b) q

Peter Larsson
Helsingborg, Sweden

Masum7
Starting Member

33 Posts

Posted - 2006-11-08 : 10:39:26

quote:
Originally posted by Peso

Yes, because DATEDIFF calculates the difference in seconds since 19000101.
There is a possibility of an overflow when adding the large dates together at seconds level.

Peter Larsson
Helsingborg, Sweden

Ya peso.. i know about datediff.. but i think putane.sanjay said @a is jst a varchar that contains "11:28:58pm" .. i think it contains no date.. so i said is there may b no reason to module..

Masum

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2006-11-09 : 02:20:15

THANK U FOR SENDING ANSWER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources