SQL Server Forums
Profile | Register | 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
 New Topic  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
2202 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
310 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000