error in concatenating 2 fields in sqlserver

Posted by Klbaiju under Sql Server on 9/30/2013 | Points: 10 | Views : 1576 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

Following query is working fine

use Baiju
SELECT
distinct
cardno, name,
min(time) over (partition by cardno, CONVERT(varchar(20), time, 102) ) entry,
max(time) over (partition by cardno, CONVERT(varchar(20), time, 102) ) [exit]
FROM Events

my requirement is i want to concatenate these 2 fields 2 fields

min(time) over (partition by cardno, CONVERT(varchar(20), time, 102) ) entry,
max(time) over (partition by cardno, CONVERT(varchar(20), time, 102) ) [exit]

like this

use baiju
SELECT
distinct
cardno, name,
min( time) over (partition by cardno, CONVERT(varchar(20), time, 102) )+'-'+ max(time) over (partition by cardno, CONVERT(varchar(20), time, 102) ) as time1
FROM Events

the error showing is

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

how to solve this

Regards

Baiju

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 9/30/2013 [Member] [MVP] Platinum | Points: 25

0	What is the data type of TIME column? and how you are storing data into that column... can you post back the sample data for TIME Column? Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 9/30/2013 [Member] [MVP] Platinum | Points: 25

0	Something you are missing to show us in the query... The error message and the query doesn't match.. Try to post exact query and add the following condition in WHERE clause once: WHERE ISDATE(TIME)=0 Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Allemahesh on: 9/30/2013 [Member] [MVP] Silver | Points: 25

0	Try the below one:- cast(min(time) over (partition by cardno, CONVERT(varchar(20), time, 102)) as varchar(100)) + '-' + CAST(max(time) over (partition by cardno, CONVERT(varchar(20), time, 102)) as varchar(100)) as time1 Happy Coding, If it helps you or directs U towards the solution, MARK IT AS ANSWER Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 10/1/2013 [Member] [MVP] Platinum | Points: 25

0	Please note the following points: 1) You should have proper datatype for TIME column as DATETIME/TIME 2) In which format you are storing DATETIME will also depends for the above error... 3) The error message which you posted is not matching with the query which you have Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Please note the following points:
1) You should have proper datatype for TIME column as DATETIME/TIME
2) In which format you are storing DATETIME will also depends for the above error...
3) The error message which you posted is not matching with the query which you have

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts