Time - 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

Time

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 01:48:21

I want to convert time: Mon Apr 28 16:42:52 PDT 2008

to

16:42:52.

Here's my statement:

SELECT timein
FROM Table

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-24 : 02:03:10

[code]
try this

SELECT CONVERT(VARCHAR(8), timein, 108)
FROM table

[/code]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:04:19

It resulted to Tue Jun. I want the results to be 16:42:52.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-24 : 02:10:18

[code]

what is the datatype of timein column.
if it is a datetime field my solution ll work, if it is a varchar field use SUBSTRING function

[/code]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:12:05

It's nvarchar. How's that?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-24 : 02:17:45

Hi,

Try this

SELECT DATEADD(d, -DATEDIFF(d, 0, timein), timein)

Be cool

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:19:02

Thanks however, it doesn't work since I still need to convert it from nvarchar.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:24:32

then use substring as already suggested. you can read all about it in Books Online

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:25:17

i tried and it's not working. can i have yours?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:26:38

what did you try?

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:27:23

SELECT timein, SUBSTRING(timein, 5, 1) AS Expr1
FROM table

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:28:39

SUBSTRING ( <expression> ,<start>,<length> )

...you can work it out from there eh?

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:29:37

it give a different results..and the column is nvarchar

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:31:05

<expression> is your column so ... timein
<start> is the position in the string to start from so in your example it's certainly NOT 5??
<length> is how many characters to select and again from your example earlier it NOT 1

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:33:53

different results.... really?


declare @a nvarchar(50)
set @a  = 'Mon Apr 28 16:42:52 PDT 2008'

select substring(@a,12,8)

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:36:04

i got it..how can i convert it into time format?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:38:24

what do you mean time format? if you want to display 'just the time' portion on it's own it can only be a string (unless you're using SQL 2008, and i doubt that you are)

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:40:20

I have results of 127. That means 127 seconds. How can I convert to minutes. Should be 2:07.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 02:41:26

you got that from the substring?? or is this a new problem?

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-07-24 : 02:43:45

yes i got it and the results is 127. How can I convert to minutes. Should be 2:07.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 02:46:02

1 Always use proper DATETIME datatype to store dates
2 Let front end application do the date formation

Madhivanan

Failing to plan is Planning to fail

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources