Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trim a date
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dimepop
Starting Member

33 Posts

Posted - 01/24/2013 :  06:52:14  Show Profile  Reply with Quote
Hi, on the query
Select time from database
Returns time like 23-Jan-131:30 PM

How can i trim the test so it only returns 1:30 PM
So remove first 9 characters
thanks

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 01/24/2013 :  06:57:45  Show Profile  Reply with Quote
quote:
Originally posted by dimepop

Hi, on the query
Select time from database
Returns time like 23-Jan-131:30 PM

How can i trim the test so it only returns 1:30 PM
So remove first 9 characters
thanks


If this is VARCHAR type data, RIGHT(yourVar, 7)



--
Chandu
Go to Top of Page

jafrywilson
Constraint Violating Yak Guru

India
379 Posts

Posted - 01/24/2013 :  06:59:46  Show Profile  Reply with Quote
select convert(varchar(10), GETDATE(), 108)

And check this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38940
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 01/24/2013 :  07:04:51  Show Profile  Reply with Quote
SELECT CONVERT(VARCHAR,GETDATE(),100)
--Output
Jan 24 2013 7:04AM

SELECT RIGHT(CONVERT(VARCHAR,GETDATE(),100),8)
--Output
7:04AM


SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

dimepop
Starting Member

33 Posts

Posted - 01/24/2013 :  07:08:01  Show Profile  Reply with Quote
Hi the datatype is smalldatetime
Regards
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 01/24/2013 :  07:40:34  Show Profile  Reply with Quote
RIGHT(CONVERT(VARCHAR,GETDATE(),100),8)

Replace GETDATE() with your date column.It should work even for datatype smalldatetime

SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

dimepop
Starting Member

33 Posts

Posted - 01/24/2013 :  07:56:40  Show Profile  Reply with Quote
HI, this worked, thanks

quote:
Originally posted by sqlbay

RIGHT(CONVERT(VARCHAR,GETDATE(),100),8)

Replace GETDATE() with your date column.It should work even for datatype smalldatetime

SQL Server Professional http://sqlbay.blogspot.in

Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
352 Posts

Posted - 01/24/2013 :  09:03:22  Show Profile  Reply with Quote
You could also use CAST(yourdatetime AS TIME) if you do not need it as a character string.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000