#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    8
    Rep Power
    0

    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)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by ryoka012
    Can anyone tell me where did i do wrong in conversion
    yes, your error was in trying to use strings

    use dates only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    8
    Rep Power
    0
    Thanks for the reply..

    Can tell me how can i fix it..
    i tried possible combination still the same error..
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by ryoka012
    Can tell me how can i fix it..
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by ryoka012
    i tried possible combination still the same error..
    did you try PatP's latest suggestion?

    http://www.dbforums.com/microsoft-sq...-function.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    8
    Rep Power
    0
    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
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    geez, do you think you posted enough data??
    Originally Posted by ryoka012
    my objective here is to ignore the minutes and seconds when using group by on B.Createdate
    you don't have to use strings to do that

    just apply your DATEDIFF and DATEADD without converting to VARCHAR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    8
    Rep Power
    0
    i have tried that but the Hour is not being displayed.

    only the yy/dd/mm
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by ryoka012
    i have tried that but the Hour is not being displayed.
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo