Get a portion of a string (split)

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)

Get a portion of a string (split)

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-17 : 17:28:57

There are many split string query online but it may be big for my case.

Can you help me to find a quick way for my case?

Mar 17 2009 12:00PM

The format is like
Month day year time.
I only want to get

12:00PM

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-17 : 19:14:06

If the field is a datetime datatype
select
substring(convert(varchar(50),getdate(),109),13,5)+RIGHT(convert(varchar(50),getdate(),109),2)

I don't think there's any quicker way, but one of the gurus here may know better

Jim

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-17 : 20:27:26

Check the behavior on that first one with an hour that has 2 digits. I think I got it right. The ltrim will lob off the space if it's an hour with 1 digit.

select ltrim(right(CAST(getdate() as varchar(20)),6))

select cast(DATEPART(HOUR,getdate()) as varchar(2)) + ':' + cast(datepart(minute,getdate()) as varchar(2))

Mike
"oh, that monkey is going to pay"

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-17 : 20:39:13

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

See something like:
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx for formats.
http://www.sqlusa.com/bestpractices2005/centurydateformat/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 03:45:25

[code]DECLARE @dt DATETIME

SET @dt = GETDATE()

SELECT @dt AS Original,
LTRIM(RIGHT(CONVERT(VARCHAR(30), @dt, 0), 7)) AS Peso

DECLARE @vc VARCHAR(30)

SET @vc = 'Mar 18 2009 8:46AM'

SELECT @vc AS Original,
LTRIM(RIGHT(@vc, 7)) AS Peso[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources