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 2008 Forums
 Transact-SQL (2008)
 Trim a date

Author  Topic 

dimepop
Starting Member

33 Posts

Posted - 2013-01-24 : 06:52:14
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 06:57:45
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

379 Posts

Posted - 2013-01-24 : 06:59:46
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 - 2013-01-24 : 07:04:51
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 - 2013-01-24 : 07:08:01
Hi the datatype is smalldatetime
Regards
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 2013-01-24 : 07:40:34
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 - 2013-01-24 : 07:56:40
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

352 Posts

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

djj
Go to Top of Page
   

- Advertisement -