Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2013
    Posts
    6

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

    Hi there,

    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
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You went wrong in converting to various formats in varchar. What is this trying to do, anyway? Add the hour portion of the datetime to the datetime value?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your code makes no sense to me, Please try to explain what you want to do using English instead of Transact-SQL.

    It looks to me like you are trying to add the hour of the day to the current time, so that 09:15 would become 18:15 and 18:15 would become 12:15 on the following day. I doubt that is what you really want!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Your mindset is still in 1960's COBOL, where temporal data was done with strings. We do not use the old 1970's Sybase CONVERT() string function today. T-SQL has temporal data types!

    CREATE TABLE Something
    (..,
    something_date DATE NOT NULL,
    ..);

    The old function still work with the DATE, TIME(n) and DATETIME2(n) data types:

    DATEADD (HOUR, something_date, @displacement_hrs)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least I think that the OP (Original Poster) is trying to do some variety of "intersting" date arithmetic. In Trnasact-SQL, that arithmetic still requires using functions.

    I'm still curious as to exactly what they were trying to accomplish.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    6
    Thanks for the reply..

    I tried to ignore the minute and seconds of the datetime..
    using this function

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

    but upon using flexgrid on vb 2005 the hour does not show only the yy/dd/mm shows..

    that is why i must convert it again to another datetime function.

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Use a table of time slots set to one more decimal second of precision than your data. You can now use temporal math to add it to a DATE to TIME(1) get a full DATETIME2(0). Here is the basic skeleton.

    CREATE TABLE Timeslots
    (slot_start_time TIME(1) NOT NULL PRIMARY KEY,
    slot_end_time TIME(1) NOT NULL,
    CHECK (start_time < end_time));

    INSERT INTO Timeslots --15 min intervals
    VALUES ('00:00:00.0', '00:14:59.9'),
    ('00:15:00.0', '00:29:59.9'),
    ('00:30:00.0', '00:44:59.9'),
    ('00:45:00.0', '01:00:59.9'),
    ..
    ('23:45:00.0', '23:59:59.9');

    Here is the basic query for rounding down to a time slot.

    SELECT CAST (@in_timestamp AS DATE), T.start_time
    FROM Timeslots AS T
    WHERE CAST (@in_timestamp AS TIME)
    BETWEEN T.slot_start_time
    AND T.slot_end_time;

  8. #8
    Join Date
    Jan 2013
    Posts
    6
    Hi thanks again for the reply..

    But there is an existing database that i must use..
    which is smalldatetime..

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ryoka012 View Post
    I tried to ignore the minute and seconds of the datetime..
    Wow, I would have never gotten there from that formula!

    I'm 99% sure that you want is:
    Code:
    Cast(B.CreateDate AS DATE)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2013
    Posts
    6
    i tried using cast but it does not work...

    my objective here is to ignore the minutes and seconds...so i can use the group by Hour on a Datetime..but when i use my front end (vb 2005) on a flexgrid it only output yy/dd/mm
    so iam trying to convert it to using below function
    convert(varchar(19),dateadd(hour, datediff(hour,CONVERT(VARCHAR(10),B.CreateDate,111 ),B.CreateDate),21)


    below is my complete query.



    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 the sample output:
    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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using:
    Code:
    SELECT DateAdd(hour, DateDiff(hour, 0, B.CreateDate), 0)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •