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 2008to 16:42:52.Here's my statement:SELECT timeinFROM 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 OnlineEm |
|
|
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 Expr1FROM 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 1Em |
|
|
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) Em |
|
|
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 dates2 Let front end application do the date formationMadhivananFailing to plan is Planning to fail |
|
|
Next Page
|