trim - 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

General SQL Server Forums

New to SQL Server Programming

trim

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-24 : 02:20:55

I have this result:

3/19/2009 12:05:43 PM

I want to trim it down to: 12:05 PM

Is that possible?

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-24 : 02:29:32

select substring('3/19/2009 12:05:43 PM',11,5) +' '+right('3/19/2009 12:05:43 PM',2)

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-24 : 02:32:02

I got that but the column name of the date is mydate. Would that will work?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-24 : 03:51:46

quote:
Originally posted by bklr

select substring('3/19/2009 12:05:43 PM',11,5) +' '+right('3/19/2009 12:05:43 PM',2)

WHAT IF MONTH IS IN TWO FIGURES LIKE NOVEMBER ????

select substring('11/19/2009 12:05:43 PM',11,5) +' '+right('11/19/2009 12:05:43 PM',2)

RESULT: 12:0 PM

Siji
Starting Member

4 Posts

Posted - 2009-03-24 : 04:31:43

SELECT SUBSTRING(CONVERT(varchar, @mydate, 100), 12, 12) As Time

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-24 : 05:32:14

quote:
Originally posted by Siji

SELECT SUBSTRING(CONVERT(varchar, @mydate, 100), 12, 12) As Time

Same problem

Take two different dates, one with month like (jan to sep) and one with moth like(oct, nov,dec) and both dates will return two different results... check this

SELECT SUBSTRING(CONVERT(varchar, '1/19/2009 12:05:43 PM', 100), 12, 12) As date
go
SELECT SUBSTRING(CONVERT(varchar, '11/19/2009 12:05:43 PM', 100), 12, 12) As date

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-24 : 05:37:30

Try this...

SELECT SUBSTRING(CONVERT(varchar,DATEADD(DAY, DATEDIFF(DAY, @mydate, '19000101'), @mydate), 100), 12, 12) AS theTimeOnly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 03:44:50

declare @d datetime
set @d='3/19/2009 12:05:43 PM'
select @d,dateadd(minute,datediff(minute,0,@d),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources