May 12, 2008 at 3:00 am
I have a application which records time in ASCII value in our database.
I need to convert the ASCII values to time format (h:m:s) in a view.
The ASCII values are store in 3 characters eg. table:
+-------+------+
|TimCol | Col2 |
+-------+-------+
| :@K | test1 |
| 838 | test2 |
| ;WV | test3 |
+-------+-------+
Please guide me to convert the ascii to date format.
Thank you.
May 12, 2008 at 3:41 am
What output would you want for the example you gave?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 12, 2008 at 4:13 am
+------------------+---------+
| TimCol | Col2 |
+------------------+---------+
| 09:03:24 AM | test1 |
| 10:15:35 AM | test2 |
| 12:10:02 PM | test3 |
+------------------+---------+
May 12, 2008 at 4:20 am
Which are all possible characters in the column TimeCol?
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 4:35 am
it seems 24hrs format, but we need in the 12 hrs format to show AM/PM.
A friend have given me access code that seems working with him
Format(Asc(Right([PrevDate], 3)) - 48 & " : " & Asc(Mid([PrevDate], 2, 1)) - 48 & " : " & Asc(Left([PrevDate], 1)) - 48, "HH:MM:SS")
May 12, 2008 at 4:56 am
I don't understand how the values are derived in your example, but perhaps you want something like this?
declare @t table (TimCol varchar(3), Col2 varchar(10))
insert @t
select ':@K', 'test1'
union all select '838', 'test2'
union all select ';WV', 'test3'
union all select 'AWV', 'test3'
select *, substring(x, 13, 8) + ' ' + substring(x, 25, 2) as Tim from (
select *, convert(varchar(26), dateadd(s, 60*60*h+60*m+s, 0), 109) x from (
select *,
ascii(substring(TimCol, 1, 1))-48 as h,
ascii(substring(TimCol, 2, 1))-48 as m,
ascii(substring(TimCol, 3, 1))-48 as s from @t) a) b
/* Results
TimCol Col2 h m s x Tim
------ ---------- ----------- ----------- ----------- -------------------------- -----------
:@K test1 10 16 27 Jan 1 1900 10:16:27:000AM 10:16:27 AM
838 test2 8 3 8 Jan 1 1900 8:03:08:000AM 8:03:08 AM
;WV test3 11 39 38 Jan 1 1900 11:39:38:000AM 11:39:38 AM
AWV test3 17 39 38 Jan 1 1900 5:39:38:000PM 5:39:38 PM
*/
You should always try to use the datetime datatype to store dates and/or times.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 12, 2008 at 5:38 am
AT&T used to have whacky date/time codes like this so they could fit date/time in 8.3 file names.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 7:36 am
Thanks,
Its working,
But the last select is not updaing the tim col.
I can make it work till the x column.
May 12, 2008 at 7:53 am
They were not using Windows, right?
I don't think : and ? are valid characters for a Windows filename.
HourCharASCII
--------------
0- 1048
1- 2159
2- 3250
3- 4351
4- 5452
5- 6553
6- 7654
7- 8755
8- 9856
9-10957
10-11:58
11-12;59
12-13<60
13-14=61
14-15>62
15-16?63
16-17@64
17-18A65
18-19B66
19-20C67
20-21D68
21-22E69
22-23F70
23-24G71
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 8:10 pm
Peso (5/12/2008)
They were not using Windows, right?I don't think : and ? are valid characters for a Windows filename.
HourCharASCII
--------------
0- 1048
1- 2159
2- 3250
3- 4351
4- 5452
5- 6553
6- 7654
7- 8755
8- 9856
9-10957
10-11:58
11-12;59
12-13<60
13-14=61
14-15>62
15-16?63
16-17@64
17-18A65
18-19B66
19-20C67
20-21D68
21-22E69
22-23F70
23-24G71
Correct.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply