truncate the date - 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

truncate the date

Author

Topic

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-23 : 12:59:17

number comments
SR10 "*** 02/06/10 12:14:51(Sandeep Singh) ***hence resolving the ticket..*** 02/06/10 12:14:21(Sandeep Singh) ***
time has been expired for the request...*** 02/06/10 08:10:42(Shiv Bhagwan) ***Please do the need full.***
01/06/10 20:49:59(Shiv Bhagwan) ***.*** 01/06/10 20:46:11(Shiv Bhagwan) ***Kindly Do the nned full.***
01/06/10 07:29:48(Abhishek .) ***team plz install the projector for conference room-103 - 12"

SR100 "*** 10/06/10 11:08:39(Poras Kumar) ***As per user, projector is no more required,Hence, resolving the
ticket as per user confirmation.*** 10/06/10 11:06:40(Poras Kumar) ***As per user, projector is no more
required.Hence resolving the ticket as per user confirmation.*** 10/06/10 08:32:16(Shiv Bhagwan) ***
Please arrange the projector at Masterpiece, 4th floor*** 10/06/10 08:14:04(Shiv Bhagwan) ***
Please arrange the projector at Masterpiece, 4th floor*** 10/06/10 07:37:17(Abhishek .) ***
Please arrange the projector at Masterpiece, 4th floorDate Timings Room No.8th June
9:30 to 11 am 401 9th June 9:30 to 11 am 40110th June 9:30 to 11 am 401

For any query please get in touch with Devyani (9711918534)"

please tell me i want only date and time from the above field

02/06/10 12:14:51
10/06/10 11:08:39

Ankita

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-23 : 13:15:14

Is it at a fixed position in that column?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-25 : 02:42:30

CREATE TABLE #tblStripDT(Remarks VARCHAR(MAX))
GO

INSERT INTO #tblStripDT Values ('SR10 "*** 02/06/10 12:14:51(Sandeep Singh) ***hence resolving the ticket..*** 02/06/10 12:14:21(Sandeep Singh) ***
time has been expired for the request...*** 02/06/10 08:10:42(Shiv Bhagwan) ***Please do the need full.***
01/06/10 20:49:59(Shiv Bhagwan) ***.*** 01/06/10 20:46:11(Shiv Bhagwan) ***Kindly Do the nned full.***
01/06/10 07:29:48(Abhishek .) ***team plz install the projector for conference room-103 - 12"')
Go

INSERT INTO #tblStripDT Values ('SR100 "*** 10/06/10 11:08:39(Poras Kumar) ***As per user, projector is no more required,Hence, resolving the
ticket as per user confirmation.*** 10/06/10 11:06:40(Poras Kumar) ***As per user, projector is no more
required.Hence resolving the ticket as per user confirmation.*** 10/06/10 08:32:16(Shiv Bhagwan) ***
Please arrange the projector at Masterpiece, 4th floor*** 10/06/10 08:14:04(Shiv Bhagwan) ***
Please arrange the projector at Masterpiece, 4th floor*** 10/06/10 07:37:17(Abhishek .) ***
Please arrange the projector at Masterpiece, 4th floorDate Timings Room No.8th June
9:30 to 11 am 401 9th June 9:30 to 11 am 40110th June 9:30 to 11 am 401')
GO

--SOLUTION: Assuming always the content will come on the same position as shown in your samples!
SELECT
REVERSE(LEFT(REVERSE(LEFT(Remarks, CHARINDEX('(', Remarks)-1)),17))
FROM #tblStripDT
GO

Best Regards
Vadivel

http://vadivel.blogspot.com

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-26 : 13:42:43

pls help me

Ankita

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-26 : 13:48:45

THNKU Vadivel

BNOW HOOW TO CONVERT IT IN DATEFORMAT

I USE CAST BUT SHOWING ERROR OUT OF RANGE DATEVALUE

Ankita

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-26 : 13:56:20

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

THIS EEROR GETING I AM

Ankita

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-26 : 23:03:52

SELECT
REVERSE(LEFT(REVERSE(LEFT(comments, CHARINDEX('(', comments)-1)),17))
FROM ocmqm1

i got an error

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of left function.

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-27 : 23:04:04

The query was written as per the sample record you have shown in your post! If it is failing then it is quite possible that you haven't given us good sample of data.

Also based on the date format you are using in your system you need to make use of SET DATEFORMAT appropriately. For example, if your dates are going to be Day month year format then use the below script:

SET DATEFORMAT DMY

Best Regards
Vadivel

http://vadivel.blogspot.com

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 01:46:38

13/09/10 18:52:12

if the range is above 12 it is showing error

i am using this query

select cast(convert(varchar(25),substring(comments,4,18),111) as datetime from tablename.

then
i get error when my range gets above the 13

error is

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 01:49:59

I think its because there are only 12 months and so once it sees 13 an error would be thrown. Did you try changing the date format?

SET DATEFORMAT DMY

Best Regards
Vadivel

http://vadivel.blogspot.com

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 01:56:43

--This code block would throw an error
--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SET DATEFORMAT MDY
GO

DECLARE @dt VARCHAR(20)
SET @dt = '13/09/10 18:52:12'

SELECT CONVERT(DATETIME, @DT)
GO

--Now it will work!
SET DATEFORMAT DMY
GO

Declare @dt VARCHAR(20)
SET @dt = '13/09/10 18:52:12'

SELECT CONVERT(DATETIME, @DT)

Best Regards
Vadivel

http://vadivel.blogspot.com

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 02:23:24

thnku lot

2010-10-21 14:49:26.000
2010-09-13 18:52:12.000
2010-09-13 18:54:52.000
2010-09-14 14:22:14.000
2010-09-13 13:39:08.000
2010-09-13 13:48:38.000
2010-09-13 19:40:15.000

i am geitng output like this

now i want to convert it inot hours pls tell me how

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 02:47:32

You are welcome :)

I think you already have a thread going for this here :) - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165999

Have responded there pls check.

Best Regards
Vadivel

http://vadivel.blogspot.com

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-09-28 : 03:20:10

are u on facebook vadivel

Ankita

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 22:14:19

yes i am http://www.facebook.com/mvadivel

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources