Argument data type varchar is invalid for argument 3 of convert function.

hi,

Can anyone tell me where did i do wrong in conversion


original query
dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate)

i tried to use convert(varchar(50),Datediff,21)
below is the exact code..
convert(varchar(50),dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate),21)

yes, your error was in trying to use strings

use dates only

Thanks for the reply..

Can tell me how can i fix it..
i tried possible combination still the same error..

sure

can you tell me what you're looking for?

i see B.CreateDate, and i see DATEADD and DATEDIFF, but what is all that trying to do?

did you try PatP's latest suggestion?

http://www.dbforums.com/microsoft-s...t-function.html

my objective here is to ignore the minutes and seconds when using group by on B.Createdate i have search and found a solution but when i run on tsql it works just fine..but when i execute it using my front end which is vb 2005 the my Hour does not being project using flexgrid.

so iam trying to convert it again on different function below.
convert(varchar(19),dateadd(hour, datediff(hour,CONVERT(VARCHAR(10),B.CreateDate,111 ),B.CreateDate),21)

here is the complete query below..


SELECT
[Hourly],
count(case when Documenttype = 1 THEN '1' END) as EDI,
count(case when Documenttype = 2 THEN '2' END) as SI,
count(case when Documenttype = 1 THEN '1' END) +
count(case when Documenttype = 2 THEN '2' END) as GrandTotal
FROM
(SELECT

dateadd(hour, datediff(hour,CONVERT(VARCHAR(10),B.CreateDate,111 ),B.CreateDate)
,CONVERT(VARCHAR(10),B.CreateDate,111)) as [Hourly]
,C.Documenttype
from bookinglinkupdateinformation A
left outer join bookinglinkinformation B
on A.Barcode = B.Barcode
left outer join bookinginformation C
on B.HID = C.ID
left join priority E
on B.Urgent = E.ID
where B.CreateDate between '2013-01-01 00:00:00' and '2013-01-31 23:59:59' and
A.Stage = 0 and
C.Flag not between '2' and '3'

) idat

group by [Hourly]

below is a output data.
2013-01-03 22:00:00.000 56 14 70
2013-01-05 02:00:00.000 13 0 13
2013-01-05 13:00:00.000 49 18 67
2013-01-09 20:00:00.000 46 21 67
2013-01-10 19:00:00.000 103 47 150
2013-01-11 18:00:00.000 104 99 203
2013-01-12 03:00:00.000 0 1 1
2013-01-14 08:00:00.000 2 10 12
2013-01-14 09:00:00.000 2 50 52
2013-01-15 11:00:00.000 28 71 99
2013-01-16 11:00:00.000 41 90 131
2013-01-17 22:00:00.000 25 24 49
2013-01-19 07:00:00.000 5 84 89
2013-01-22 05:00:00.000 21 109 130
2013-01-22 15:00:00.000 112 88 200
2013-01-23 01:00:00.000 16 52 68
2013-01-24 01:00:00.000 34 15 49
2013-01-25 03:00:00.000 54 40 94
2013-01-26 04:00:00.000 0 17 17
2013-01-28 09:00:00.000 29 40 69
2013-01-28 19:00:00.000 36 24 60
2013-01-29 18:00:00.000 45 53 98
2013-01-30 12:00:00.000 99 99 198
2013-01-30 16:00:00.000 73 84 157
2013-01-30 17:00:00.000 96 94 190
2013-01-04 03:00:00.000 2 4 6
2013-01-05 07:00:00.000 15 34 49
2013-01-07 10:00:00.000 31 88 119
2013-01-07 14:00:00.000 26 46 72
2013-01-07 21:00:00.000 14 14 28
2013-01-09 00:00:00.000 19 27 46
2013-01-09 10:00:00.000 52 51 103
2013-01-09 14:00:00.000 93 55 148
2013-01-10 01:00:00.000 21 50 71
2013-01-11 08:00:00.000 30 106 136
2013-01-12 01:00:00.000 17 35 52
2013-01-14 10:00:00.000 12 52 64
2013-01-14 20:00:00.000 24 10 34
2013-01-15 22:00:00.000 28 17 45
2013-01-16 10:00:00.000 58 80 138
2013-01-16 14:00:00.000 77 41 118
2013-01-18 08:00:00.000 37 115 152
2013-01-18 09:00:00.000 110 156 266
2013-01-19 05:00:00.000 17 39 56
2013-01-21 04:00:00.000 11 6 17
2013-01-21 05:00:00.000 1 3 4
2013-01-21 23:00:00.000 33 81 114
2013-01-22 22:00:00.000 43 23 66
2013-01-23 03:00:00.000 0 20 20
2013-01-23 18:00:00.000 81 80 161
2013-01-25 18:00:00.000 99 83 182
2013-01-26 05:00:00.000 35 12 47
2013-01-26 15:00:00.000 0 1 1
2013-01-29 06:00:00.000 39 39 78
2013-01-29 12:00:00.000 54 70 124
2013-01-29 13:00:00.000 73 51 124
2013-01-29 16:00:00.000 83 88 171
2013-01-30 03:00:00.000 1 6 7
2013-01-02 11:00:00.000 23 62 85
2013-01-03 00:00:00.000 17 23 40
2013-01-05 06:00:00.000 17 44 61
2013-01-05 17:00:00.000 6 1 7
2013-01-07 22:00:00.000 15 28 43
2013-01-08 18:00:00.000 69 34 103
2013-01-09 02:00:00.000 9 33 42
2013-01-09 21:00:00.000 37 11 48
2013-01-10 08:00:00.000 84 61 145
2013-01-10 09:00:00.000 95 110 205
2013-01-12 11:00:00.000 10 31 41
2013-01-15 03:00:00.000 4 1 5
2013-01-15 18:00:00.000 86 61 147
2013-01-16 07:00:00.000 17 61 78
2013-01-17 00:00:00.000 20 22 42
2013-01-17 10:00:00.000 75 93 168
2013-01-17 14:00:00.000 116 71 187
2013-01-18 05:00:00.000 0 43 43
2013-01-18 15:00:00.000 123 111 234
2013-01-19 11:00:00.000 13 30 43
2013-01-21 22:00:00.000 21 13 34
2013-01-22 04:00:00.000 7 45 52
2013-01-22 23:00:00.000 30 52 82
2013-01-23 07:00:00.000 8 50 58
2013-01-24 09:00:00.000 47 50 97
2013-01-24 19:00:00.000 88 73 161
2013-01-26 12:00:00.000 1 10 11
2013-01-26 16:00:00.000 2 7 9
2013-01-26 17:00:00.000 0 1 1
2013-01-28 08:00:00.000 16 10 26
2013-01-29 03:00:00.000 2 2 4
2013-01-30 06:00:00.000 38 64 102
2013-01-30 13:00:00.000 124 85 209
2013-01-02 18:00:00.000 28 29 57
2013-01-03 01:00:00.000 31 9 40
2013-01-03 07:00:00.000 14 75 89
2013-01-04 18:00:00.000 72 80 152
2013-01-05 01:00:00.000 15 29 44
2013-01-05 08:00:00.000 22 15 37
2013-01-08 06:00:00.000 11 104 115
2013-01-08 12:00:00.000 92 62 154
2013-01-08 13:00:00.000 66 25 91
2013-01-08 16:00:00.000 49 94 143
2013-01-09 22:00:00.000 23 33 56
2013-01-10 07:00:00.000 23 33 56
2013-01-11 15:00:00.000 109 103 212
2013-01-12 07:00:00.000 9 50 59
2013-01-14 21:00:00.000 21 15 36
2013-01-16 02:00:00.000 11 36 47
2013-01-16 20:00:00.000 39 27 66
2013-01-16 21:00:00.000 33 15 48
2013-01-17 03:00:00.000 6 3 9
2013-01-18 19:00:00.000 107 60 167
2013-01-19 04:00:00.000 32 46 78
2013-01-21 12:00:00.000 55 69 124
2013-01-21 17:00:00.000 71 72 143
2013-01-22 00:00:00.000 41 52 93
2013-01-22 10:00:00.000 73 61 134
2013-01-22 14:00:00.000 78 52 130
2013-01-23 11:00:00.000 67 118 185
2013-01-24 00:00:00.000 15 45 60
2013-01-24 22:00:00.000 31 20 51
2013-01-25 11:00:00.000 90 124 214
2013-01-28 15:00:00.000 44 63 107
2013-01-31 01:00:00.000 26 41 67
2013-01-02 08:00:00.000 13 20 33
2013-01-02 19:00:00.000 23 15 38
2013-01-04 22:00:00.000 28 37 65
2013-01-05 04:00:00.000 21 76 97
2013-01-05 15:00:00.000 38 8 46
2013-01-07 18:00:00.000 42 37 79
2013-01-09 12:00:00.000 131 70 201
2013-01-09 16:00:00.000 107 116 223
2013-01-09 17:00:00.000 26 99 125
2013-01-10 05:00:00.000 25 11 36
2013-01-10 15:00:00.000 125 97 222
2013-01-11 11:00:00.000 48 135 183
2013-01-14 04:00:00.000 11 7 18
2013-01-14 23:00:00.000 42 77 119
2013-01-15 07:00:00.000 8 64 72
2013-01-16 03:00:00.000 5 4 9
2013-01-16 18:00:00.000 93 61 154
2013-01-17 02:00:00.000 18 12 30
2013-01-17 21:00:00.000 19 19 38
2013-01-18 12:00:00.000 152 117 269
2013-01-18 16:00:00.000 139 122 261
2013-01-18 17:00:00.000 131 120 251
2013-01-19 01:00:00.000 24 23 47
2013-01-21 20:00:00.000 53 26 79
2013-01-21 21:00:00.000 26 26 52
2013-01-22 06:00:00.000 18 40 58
2013-01-22 13:00:00.000 50 40 90
2013-01-23 09:00:00.000 37 81 118
2013-01-23 19:00:00.000 71 46 117
2013-01-24 07:00:00.000 7 25 32
2013-01-25 00:00:00.000 41 42 83
2013-01-25 10:00:00.000 87 165 252
2013-01-25 14:00:00.000 137 96 233
2013-01-26 02:00:00.000 7 55 62
2013-01-29 22:00:00.000 25 23 48
2013-01-30 20:00:00.000 64 17 81
2013-01-02 07:00:00.000 0 2 2
2013-01-03 18:00:00.000 172 43 215
2013-01-04 00:00:00.000 23 46 69
2013-01-04 10:00:00.000 18 55 73
2013-01-04 14:00:00.000 19 45 64
2013-01-04 21:00:00.000 47 26 73
2013-01-05 05:00:00.000 9 26 35
2013-01-05 12:00:00.000 6 24 30
2013-01-05 16:00:00.000 17 13 30
2013-01-08 22:00:00.000 32 27 59
2013-01-09 06:00:00.000 20 21 41
2013-01-09 13:00:00.000 78 76 154
2013-01-11 07:00:00.000 37 61 98
2013-01-14 05:00:00.000 9 11 20
2013-01-14 15:00:00.000 56 66 122
2013-01-15 01:00:00.000 13 59 72
2013-01-17 20:00:00.000 88 19 107
2013-01-18 04:00:00.000 8 35 43
2013-01-18 23:00:00.000 27 73 100
2013-01-19 09:00:00.000 5 28 33
2013-01-19 19:00:00.000 4 0 4
2013-01-21 10:00:00.000 25 74 99
2013-01-21 14:00:00.000 84 76 160
2013-01-22 12:00:00.000 102 67 169
2013-01-22 16:00:00.000 79 83 162
2013-01-22 17:00:00.000 51 118 169
2013-01-23 08:00:00.000 24 64 88
2013-01-24 11:00:00.000 109 73 182
2013-01-25 22:00:00.000 42 20 62
2013-01-26 06:00:00.000 17 75 92
2013-01-26 13:00:00.000 6 24 30
2013-01-28 07:00:00.000 3 14 17
2013-01-30 02:00:00.000 16 25 41
2013-01-30 21:00:00.000 23 20 43
2013-01-02 12:00:00.000 12 62 74
2013-01-02 16:00:00.000 85 80 165
2013-01-02 23:00:00.000 47 52 99
2013-01-03 02:00:00.000 17 52 69
2013-01-03 13:00:00.000 26 23 49
2013-01-03 20:00:00.000 24 14 38
2013-01-04 06:00:00.000 23 68 91
2013-01-04 16:00:00.000 27 83 110
2013-01-04 17:00:00.000 24 112 136
2013-01-04 23:00:00.000 40 71 111
2013-01-07 07:00:00.000 3 4 7
2013-01-08 07:00:00.000 6 84 90
2013-01-09 08:00:00.000 43 45 88
2013-01-09 09:00:00.000 56 87 143
2013-01-10 02:00:00.000 17 28 45
2013-01-10 21:00:00.000 23 22 45
2013-01-11 00:00:00.000 26 73 99
2013-01-12 02:00:00.000 18 34 52
2013-01-14 19:00:00.000 44 35 79
2013-01-15 15:00:00.000 93 49 142
2013-01-16 09:00:00.000 43 79 122
2013-01-16 19:00:00.000 66 34 100
2013-01-17 15:00:00.000 84 123 207
2013-01-18 00:00:00.000 16 40 56
2013-01-18 14:00:00.000 174 117 291
2013-01-23 04:00:00.000 20 29 49
2013-01-23 23:00:00.000 45 41 86
2013-01-24 15:00:00.000 105 85 190
2013-01-25 12:00:00.000 147 135 282
2013-01-25 17:00:00.000 146 114 260
2013-01-29 11:00:00.000 59 64 123
2013-01-30 04:00:00.000 17 99 116
2013-01-30 23:00:00.000 37 60 97
2013-01-03 03:00:00.000 15 3 18
2013-01-04 07:00:00.000 7 17 24
2013-01-05 09:00:00.000 42 3 45
2013-01-07 06:00:00.000 1 4 5
2013-01-07 17:00:00.000 61 61 122
2013-01-08 00:00:00.000 13 32 45
2013-01-08 10:00:00.000 39 73 112
2013-01-08 14:00:00.000 79 31 110
2013-01-10 11:00:00.000 68 65 133
2013-01-11 01:00:00.000 26 61 87
2013-01-12 08:00:00.000 44 98 142
2013-01-14 13:00:00.000 28 32 60
2013-01-15 10:00:00.000 25 65 90
2013-01-15 14:00:00.000 78 67 145
2013-01-15 20:00:00.000 27 12 39
2013-01-17 18:00:00.000 87 70 157
2013-01-18 01:00:00.000 35 24 59
2013-01-18 07:00:00.000 27 61 88
2013-01-19 08:00:00.000 7 13 20
2013-01-21 06:00:00.000 7 4 11
2013-01-21 13:00:00.000 73 53 126
2013-01-21 16:00:00.000 74 103 177
2013-01-22 20:00:00.000 28 10 38
2013-01-23 22:00:00.000 30 23 53
2013-01-24 03:00:00.000 15 7 22
2013-01-24 18:00:00.000 124 101 225
2013-01-25 07:00:00.000 39 68 107
2013-01-26 08:00:00.000 1 22 23
2013-01-26 09:00:00.000 15 86 101
2013-01-28 12:00:00.000 49 54 103
2013-01-28 16:00:00.000 58 59 117
2013-01-29 02:00:00.000 12 10 22
2013-01-29 20:00:00.000 43 11 54
2013-01-29 21:00:00.000 29 22 51
2013-01-30 22:00:00.000 30 22 52
2013-01-31 03:00:00.000 0 1 1
2013-01-03 04:00:00.000 1 2 3
2013-01-04 08:00:00.000 4 55 59
2013-01-04 09:00:00.000 10 34 44
2013-01-04 19:00:00.000 54 42 96
2013-01-07 05:00:00.000 14 5 19
2013-01-07 15:00:00.000 46 49 95
2013-01-08 15:00:00.000 56 73 129
2013-01-09 18:00:00.000 50 65 115
2013-01-11 12:00:00.000 161 101 262
2013-01-11 16:00:00.000 189 116 305
2013-01-12 04:00:00.000 13 7 20
2013-01-12 05:00:00.000 27 6 33
2013-01-14 18:00:00.000 39 23 62
2013-01-15 06:00:00.000 28 39 67
2013-01-15 13:00:00.000 53 32 85
2013-01-16 17:00:00.000 90 93 183
2013-01-17 07:00:00.000 30 60 90
2013-01-18 11:00:00.000 45 105 150
2013-01-18 18:00:00.000 87 70 157
2013-01-19 02:00:00.000 9 41 50
2013-01-21 19:00:00.000 56 35 91
2013-01-23 10:00:00.000 97 92 189
2013-01-23 14:00:00.000 82 78 160
2013-01-23 20:00:00.000 43 22 65
2013-01-24 06:00:00.000 49 96 145
2013-01-24 13:00:00.000 108 45 153
2013-01-25 09:00:00.000 100 158 258
2013-01-26 01:00:00.000 21 45 66
2013-01-26 07:00:00.000 10 66 76
2013-01-28 20:00:00.000 32 23 55
2013-01-28 21:00:00.000 48 17 65
2013-01-29 08:00:00.000 8 59 67
2013-01-30 01:00:00.000 16 33 49
2013-01-30 19:00:00.000 56 35 91
2013-01-02 13:00:00.000 16 28 44
2013-01-03 08:00:00.000 4 90 94
2013-01-03 19:00:00.000 41 28 69
2013-01-04 04:00:00.000 19 33 52
2013-01-04 15:00:00.000 40 67 107
2013-01-05 03:00:00.000 1 4 5
2013-01-07 09:00:00.000 12 36 48
2013-01-08 17:00:00.000 46 59 105
2013-01-09 07:00:00.000 32 100 132
2013-01-10 00:00:00.000 15 58 73
2013-01-10 14:00:00.000 90 62 152
2013-01-11 05:00:00.000 32 14 46
2013-01-12 06:00:00.000 25 52 77
2013-01-12 12:00:00.000 2 7 9
2013-01-12 13:00:00.000 37 26 63
2013-01-12 16:00:00.000 7 6 13
2013-01-14 22:00:00.000 34 19 53
2013-01-15 04:00:00.000 19 45 64
2013-01-15 17:00:00.000 50 81 131
2013-01-15 23:00:00.000 24 45 69
2013-01-17 08:00:00.000 33 84 117
2013-01-17 09:00:00.000 57 87 144
2013-01-19 12:00:00.000 1 7 8
2013-01-19 16:00:00.000 6 7 13
2013-01-19 17:00:00.000 3 9 12
2013-01-21 08:00:00.000 37 6 43
2013-01-22 03:00:00.000 1 27 28
2013-01-23 06:00:00.000 35 59 94
2013-01-23 13:00:00.000 71 77 148
2013-01-24 10:00:00.000 66 81 147
2013-01-24 14:00:00.000 126 68 194
2013-01-25 15:00:00.000 139 151 290
2013-01-26 11:00:00.000 8 22 30
2013-01-26 18:00:00.000 0 1 1
2013-01-29 04:00:00.000 0 35 35
2013-01-29 05:00:00.000 40 87 127
2013-01-29 23:00:00.000 30 41 71
2013-01-03 12:00:00.000 58 48 106
2013-01-03 16:00:00.000 48 60 108
2013-01-03 23:00:00.000 16 42 58
2013-01-04 02:00:00.000 24 5 29
2013-01-04 13:00:00.000 99 32 131
2013-01-04 20:00:00.000 76 33 109
2013-01-07 11:00:00.000 21 60 81
2013-01-08 09:00:00.000 23 58 81
2013-01-09 05:00:00.000 14 64 78
2013-01-09 15:00:00.000 52 78 130
2013-01-10 12:00:00.000 96 65 161
2013-01-10 16:00:00.000 128 138 266
2013-01-10 17:00:00.000 122 105 227
2013-01-11 02:00:00.000 30 49 79
2013-01-11 20:00:00.000 68 71 139
2013-01-11 21:00:00.000 64 16 80
2013-01-15 09:00:00.000 20 50 70
2013-01-15 19:00:00.000 49 29 78
2013-01-16 15:00:00.000 59 92 151
2013-01-17 12:00:00.000 138 80 218
2013-01-17 17:00:00.000 98 133 231
2013-01-18 02:00:00.000 48 24 72
2013-01-18 21:00:00.000 28 22 50
2013-01-19 00:00:00.000 27 83 110
2013-01-21 11:00:00.000 46 109 155
2013-01-22 19:00:00.000 89 43 132
2013-01-23 15:00:00.000 104 97 201
2013-01-24 17:00:00.000 149 81 230
2013-01-26 00:00:00.000 42 59 101
2013-01-26 10:00:00.000 2 33 35
2013-01-26 14:00:00.000 3 9 12
2013-01-28 11:00:00.000 47 73 120
2013-01-29 01:00:00.000 24 58 82
2013-01-29 19:00:00.000 67 33 100
2013-01-02 22:00:00.000 54 14 68
2013-01-03 11:00:00.000 57 61 118
2013-01-04 11:00:00.000 182 43 225
2013-01-07 13:00:00.000 55 36 91
2013-01-07 20:00:00.000 33 19 52
2013-01-08 02:00:00.000 9 49 58
2013-01-08 20:00:00.000 35 5 40
2013-01-08 21:00:00.000 12 23 35
2013-01-09 03:00:00.000 0 7 7
2013-01-10 03:00:00.000 2 6 8
2013-01-10 18:00:00.000 105 71 176
2013-01-11 09:00:00.000 62 124 186
2013-01-11 19:00:00.000 84 61 145
2013-01-12 09:00:00.000 3 72 75
2013-01-14 12:00:00.000 21 51 72
2013-01-14 16:00:00.000 61 52 113
2013-01-14 17:00:00.000 71 57 128
2013-01-15 00:00:00.000 30 27 57
2013-01-16 00:00:00.000 34 44 78
2013-01-16 22:00:00.000 22 21 43
2013-01-17 11:00:00.000 34 105 139
2013-01-19 15:00:00.000 16 4 20
2013-01-22 02:00:00.000 5 0 5
2013-01-22 21:00:00.000 27 31 58
2013-01-25 01:00:00.000 33 31 64
2013-01-25 19:00:00.000 64 53 117
2013-01-28 17:00:00.000 62 70 132
2013-01-28 23:00:00.000 45 77 122
2013-01-29 00:00:00.000 51 32 83
2013-01-29 10:00:00.000 37 70 107
2013-01-29 14:00:00.000 111 56 167
2013-01-30 00:00:00.000 34 24 58
2013-01-30 10:00:00.000 70 123 193
2013-01-30 14:00:00.000 68 65 133
2013-01-02 10:00:00.000 20 22 42
2013-01-02 14:00:00.000 20 48 68
2013-01-02 20:00:00.000 41 3 44
2013-01-02 21:00:00.000 19 9 28
2013-01-03 05:00:00.000 20 10 30
2013-01-03 15:00:00.000 55 49 104
2013-01-04 01:00:00.000 18 57 75
2013-01-05 18:00:00.000 7 0 7
2013-01-07 12:00:00.000 48 41 89
2013-01-07 16:00:00.000 99 74 173
2013-01-07 23:00:00.000 26 36 62
2013-01-08 08:00:00.000 5 32 37
2013-01-09 11:00:00.000 31 98 129
2013-01-10 22:00:00.000 31 31 62
2013-01-11 06:00:00.000 34 97 131
2013-01-11 13:00:00.000 144 74 218
2013-01-12 15:00:00.000 36 11 47
2013-01-15 02:00:00.000 7 21 28
2013-01-15 21:00:00.000 36 15 51
2013-01-16 04:00:00.000 32 36 68
2013-01-16 05:00:00.000 0 42 42
2013-01-16 23:00:00.000 48 60 108
2013-01-17 01:00:00.000 40 51 91
2013-01-17 19:00:00.000 77 27 104
2013-01-18 03:00:00.000 0 10 10
2013-01-19 10:00:00.000 16 12 28
2013-01-19 14:00:00.000 30 8 38
2013-01-21 09:00:00.000 20 57 77
2013-01-22 11:00:00.000 57 70 127
2013-01-22 18:00:00.000 46 22 68
2013-01-23 00:00:00.000 54 16 70
2013-01-24 12:00:00.000 104 132 236
2013-01-24 16:00:00.000 144 108 252
2013-01-25 08:00:00.000 51 148 199
2013-01-28 13:00:00.000 51 40 91
2013-01-29 15:00:00.000 73 81 154
2013-01-30 07:00:00.000 19 37 56
2013-01-02 06:00:00.000 0 1 1
2013-01-02 17:00:00.000 51 60 111
2013-01-03 09:00:00.000 11 102 113
2013-01-05 11:00:00.000 22 17 39
2013-01-07 04:00:00.000 4 4 8
2013-01-08 04:00:00.000 14 6 20
2013-01-08 05:00:00.000 31 59 90
2013-01-08 23:00:00.000 29 42 71
2013-01-09 01:00:00.000 13 39 52
2013-01-09 19:00:00.000 88 33 121
2013-01-10 10:00:00.000 98 112 210
2013-01-10 20:00:00.000 53 21 74
2013-01-11 04:00:00.000 19 9 28
2013-01-11 17:00:00.000 63 107 170
2013-01-11 23:00:00.000 47 62 109
2013-01-12 17:00:00.000 1 0 1
2013-01-14 14:00:00.000 49 54 103
2013-01-15 12:00:00.000 56 55 111
2013-01-15 16:00:00.000 80 99 179
2013-01-16 06:00:00.000 36 49 85
2013-01-16 12:00:00.000 118 67 185
2013-01-16 13:00:00.000 59 59 118
2013-01-16 16:00:00.000 106 125 231
2013-01-18 22:00:00.000 32 18 50
2013-01-19 06:00:00.000 12 75 87
2013-01-19 13:00:00.000 31 21 52
2013-01-21 15:00:00.000 28 39 67
2013-01-23 02:00:00.000 24 15 39
2013-01-23 21:00:00.000 28 18 46
2013-01-24 02:00:00.000 33 68 101
2013-01-24 20:00:00.000 69 23 92
2013-01-24 21:00:00.000 24 18 42
2013-01-25 04:00:00.000 6 18 24
2013-01-25 05:00:00.000 3 3 6
2013-01-25 23:00:00.000 32 26 58
2013-01-28 10:00:00.000 24 52 76
2013-01-28 14:00:00.000 68 60 128
2013-01-29 17:00:00.000 58 88 146
2013-01-30 11:00:00.000 58 83 141
2013-01-30 18:00:00.000 80 80 160
2013-01-31 00:00:00.000 41 35 76
2013-01-02 09:00:00.000 4 60 64
2013-01-03 06:00:00.000 46 87 133
2013-01-03 17:00:00.000 76 61 137
2013-01-08 01:00:00.000 29 9 38
2013-01-08 19:00:00.000 53 24 77
2013-01-09 04:00:00.000 0 2 2
2013-01-09 23:00:00.000 23 32 55
2013-01-10 04:00:00.000 15 34 49
2013-01-10 23:00:00.000 45 47 92
2013-01-11 10:00:00.000 74 101 175
2013-01-11 14:00:00.000 127 120 247
2013-01-12 00:00:00.000 11 51 62
2013-01-12 10:00:00.000 12 23 35
2013-01-12 14:00:00.000 27 13 40
2013-01-14 11:00:00.000 19 74 93
2013-01-15 08:00:00.000 11 16 27
2013-01-16 08:00:00.000 21 93 114
2013-01-17 06:00:00.000 36 86 122
2013-01-17 13:00:00.000 94 72 166
2013-01-17 16:00:00.000 148 125 273
2013-01-18 06:00:00.000 80 81 161
2013-01-18 13:00:00.000 199 65 264
2013-01-21 07:00:00.000 2 5 7
2013-01-22 01:00:00.000 17 35 52
2013-01-22 07:00:00.000 7 39 46
2013-01-23 05:00:00.000 25 68 93
2013-01-24 04:00:00.000 8 14 22
2013-01-24 05:00:00.000 49 68 117
2013-01-24 23:00:00.000 21 51 72
2013-01-25 02:00:00.000 2 27 29
2013-01-25 20:00:00.000 52 75 127
2013-01-25 21:00:00.000 32 12 44
2013-01-28 18:00:00.000 82 32 114
2013-01-29 09:00:00.000 43 51 94
2013-01-30 08:00:00.000 33 89 122
2013-01-30 09:00:00.000 32 72 104
2013-01-02 05:00:00.000 0 4 4
2013-01-02 15:00:00.000 125 52 177
2013-01-03 10:00:00.000 20 61 81
2013-01-03 14:00:00.000 25 65 90
2013-01-03 21:00:00.000 103 16 119
2013-01-04 05:00:00.000 24 74 98
2013-01-04 12:00:00.000 74 67 141
2013-01-05 00:00:00.000 16 45 61
2013-01-05 10:00:00.000 17 16 33
2013-01-05 14:00:00.000 19 11 30
2013-01-07 08:00:00.000 12 3 15
2013-01-07 19:00:00.000 55 12 67
2013-01-08 11:00:00.000 27 61 88
2013-01-10 06:00:00.000 47 119 166
2013-01-10 13:00:00.000 115 74 189
2013-01-11 22:00:00.000 39 19 58
2013-01-14 07:00:00.000 6 24 30
2013-01-15 05:00:00.000 10 50 60
2013-01-16 01:00:00.000 24 55 79
2013-01-17 04:00:00.000 41 51 92
2013-01-17 05:00:00.000 6 54 60
2013-01-17 23:00:00.000 38 46 84
2013-01-18 10:00:00.000 94 135 229
2013-01-18 20:00:00.000 44 94 138
2013-01-19 03:00:00.000 0 1 1
2013-01-19 18:00:00.000 0 21 21
2013-01-21 18:00:00.000 69 47 116
2013-01-22 08:00:00.000 0 23 23
2013-01-22 09:00:00.000 7 52 59
2013-01-23 12:00:00.000 88 46 134
2013-01-23 16:00:00.000 131 83 214
2013-01-23 17:00:00.000 130 111 241
2013-01-24 08:00:00.000 30 116 146
2013-01-25 06:00:00.000 58 16 74
2013-01-25 13:00:00.000 136 78 214
2013-01-25 16:00:00.000 151 138 289
2013-01-28 22:00:00.000 37 32 69
2013-01-29 07:00:00.000 50 102 152
2013-01-30 05:00:00.000 25 60 85
2013-01-30 15:00:00.000 110 102 212
2013-01-31 02:00:00.000 46 75 121

geez, do you think you posted enough data?? you don't have to use strings to do that

just apply your DATEDIFF and DATEADD without converting to VARCHAR

i have tried that but the Hour is not being displayed.

only the yy/dd/mm

if it works okay when you test the query, but doesn't display the hour when you run the query from vb, then it isn't a database problem, is it

would you like me to move this thread to the vb forum for you?