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.
| Author |
Topic |
|
glendcruz
Yak Posting Veteran
60 Posts |
 Posted - 2010-11-21 : 00:53:20
|
| I have a table constructed like thisEg -- A consised version Declare @t Table ( EmpId int , Date datetime Clocked_in datetime, clocked_out datetime)Some one has entered data in the clocked_in and clocked_out like this select 1, '2010/10/19' '0555', '1803'select 2, '2010/10/20', '1558', '1800'How do i write the select statement that expresses the clocked_in and clocked_outtime as 05:55AM and 06:03PM etcAny help will be appreciated |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-11-21 : 03:49:44
|
| First of all it does not make sense.You have a column Clocked_in and clocked_out which are of data type datetime.Then how is it possible that a value '0555' and '1803' got entered in these columns?PBUH |
 |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
Posted - 2010-11-21 : 17:31:34
|
I cannnot understand this myselef, However there is a coma between 05,55 and the following records. Thanks for your replyquote:
Originally posted by Sachin.Nand
First of all it does not make sense.You have a column Clocked_in and clocked_out which are of data type datetime.Then how is it possible that a value '0555' and '1803' got entered in these columns?PBUH
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-21 : 17:38:03
|
| what happens when you do a select?It should display as a datetime.It is possible that someone has used an incorrect datatype mapping in which case there could be invalid data in the columns - in which case you will need to overwrite it witha correct datetime.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
glendcruz
Yak Posting Veteran
60 Posts |
Posted - 2010-11-22 : 18:46:51
|
quote:
Originally posted by nigelrivettI have just joined this company, and everyone seems to be prety tight lipped.I intend overwritng it with the right dattime format.Thanks
what happens when you do a select?It should display as a datetime.It is possible that someone has used an incorrect datatype mapping in which case there could be invalid data in the columns - in which case you will need to overwrite it witha correct datetime.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
bobmcclellan
Starting Member
46 Posts |
Posted - 2010-11-22 : 23:57:46
|
| Does this get you there...select 1, '2010/10/19', [Clocked In] = LEFT('0555',2) + ':'+RIGHT('0555',2) + CASE When LEFT('0555',2) < 12 then ' AM' else ' PM' END, [Clocked Out] = LEFT('1803',2) + ':'+RIGHT('1803',2) + CASE When LEFT('1803',2) < 12 then ' AM' else ' PM' ENDunion all select 2, '2010/10/20', [Clocked In] = LEFT('0558',2) + ':'+RIGHT('0558',2) + CASE When LEFT('0558',2) < 12 then ' AM' else ' PM' END, [Clocked Out] = LEFT('1800',2) + ':'+RIGHT('1800',2) + CASE When LEFT('1800',2) < 12 then ' AM' else ' PM' END |
|
|
|
bobmcclellan
Starting Member
46 Posts |
Posted - 2010-11-23 : 08:45:39
|
| Realized while driving in this morning that this does nothing for you.converting the 24 hr time to 12 hr time is what you need. Too many hours yesterday.As Sachin posted, how can the string reside in a datetime field? |
|
|
|
|
|
|
|
|
Time conversion