none
Format field to a date format RRS feed

  • Question

  • Hello,

    I'm writing a report that pulls up information that is created on certain date range. However the date creation field is on the format YYYYMMDD (seems like it is an integer type). Users will need to enter the input date range as a datetime type... Is there a way to do this?

    This is how I write my query, using CAST function. But I keep getting an error "Arithmetic overflow error converting expression to data type datetime".

    SELECT     customerid, customername, dateID
    FROM         tableA
    WHERE     (CAST(dateID AS datetime) <= @enddate) AND (CAST(dateID AS datetime) >= @begindate)

    In the report parameter I set up the input parameter enddate and begindate as DateTime type (because users want to input it that way).

    Could anyone please help me on this one? Thanks in advanced for your help.
    Wednesday, December 16, 2009 8:40 PM

Answers

  • Hello,

    I'm writing a report that pulls up information that is created on certain date range. However the date creation field is on the format YYYYMMDD (seems like it is an integer type). Users will need to enter the input date range as a datetime type... Is there a way to do this?

    This is how I write my query, using CAST function. But I keep getting an error "Arithmetic overflow error converting expression to data type datetime".

    SELECT     customerid, customername, dateID
    FROM         tableA
    WHERE     (CAST(dateID AS datetime) <= @enddate) AND (CAST(dateID AS datetime) >= @begindate)

    In the report parameter I set up the input parameter enddate and begindate as DateTime type (because users want to input it that way).

    Could anyone please help me on this one? Thanks in advanced for your help.
    your dateID is int or numeric, what you can do is convert it as char then as date CAST(CAST(dateID AS char(8)) AS dateTime) hope this works alan
    • Marked as answer by kurobenko Thursday, December 17, 2009 6:22 PM
    Thursday, December 17, 2009 9:20 AM
  • Hi,
      try this :

    SELECT     customerid, customername, Cast(dateID as date)dateID
    FROM         tableA
    WHERE     (CAST(dateID AS date) <= cast(@enddate as date)) AND (CAST(dateID AS date) >= Cast(@begindate as Date))


    and take the datatype of @enddate and @begindate parameters as datetime in report....

    Aftab Ansari
    • Marked as answer by kurobenko Thursday, December 17, 2009 6:22 PM
    Thursday, December 17, 2009 10:02 AM

All replies

  • Hello,

    I'm writing a report that pulls up information that is created on certain date range. However the date creation field is on the format YYYYMMDD (seems like it is an integer type). Users will need to enter the input date range as a datetime type... Is there a way to do this?

    This is how I write my query, using CAST function. But I keep getting an error "Arithmetic overflow error converting expression to data type datetime".

    SELECT     customerid, customername, dateID
    FROM         tableA
    WHERE     (CAST(dateID AS datetime) <= @enddate) AND (CAST(dateID AS datetime) >= @begindate)

    In the report parameter I set up the input parameter enddate and begindate as DateTime type (because users want to input it that way).

    Could anyone please help me on this one? Thanks in advanced for your help.
    your dateID is int or numeric, what you can do is convert it as char then as date CAST(CAST(dateID AS char(8)) AS dateTime) hope this works alan
    • Marked as answer by kurobenko Thursday, December 17, 2009 6:22 PM
    Thursday, December 17, 2009 9:20 AM
  • Hi,
      try this :

    SELECT     customerid, customername, Cast(dateID as date)dateID
    FROM         tableA
    WHERE     (CAST(dateID AS date) <= cast(@enddate as date)) AND (CAST(dateID AS date) >= Cast(@begindate as Date))


    and take the datatype of @enddate and @begindate parameters as datetime in report....

    Aftab Ansari
    • Marked as answer by kurobenko Thursday, December 17, 2009 6:22 PM
    Thursday, December 17, 2009 10:02 AM
  • Aftab and Alan, this is working well now. Thank you very much for your help!
    Thursday, December 17, 2009 6:22 PM