error in concatenating 2 fields in sqlserver

Posted by Klbaiju under Sql Server on 9/30/2013 | Points: 10 | Views : 1025 | Status : [Member] | Replies : 4
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response