none
Error 242 during a SQL Job RRS feed

  • Question

  • Hello everyone,

    I have a strange issue here and i really do not know what i can do about it. We have a batch running every day on the SQL server that creates a new Job on the SQL server. This Job is scheduled to kickoff a DTS package and inside this DTS package there are several stored procedures that will run and fill the tables with data.

    Now this batch is running fine for about a year long now, until a week ago. Now this Job gives me an error message everytime it runs a specific stored procedure inside the DTS package. The error message is: 

     

    Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data
    type resulted in an out-of-range datetime value.

     

    Now the query where this happens is the following query:

    insert into ShP_VBC_RAP_Verwerkt_Per_Medewerker
    (
     Datum, Medewerker, Aantal, Status
    )
    select convert(varchar, created,101) datum, medewerker, count(*) aantal, status
    from ShP_VBC_OudeWerklijst
    group by medewerker, status, convert(varchar, created,101)
    order by medewerker, status asc

    Now this is just a query to fill some data that is used for reports and it is not really a complicated query. The 'created' and 'Datum' fields are DateTime fields inside the tables.

     

    The strange part of this story is that when i run this query inside the Query Analyzer it runs fine, if i run the specific action inside the DTS package (for this query) it runs fine. The error message only happens when i run it in combination with the created SQL Job...

    Does anybody have any idea why this error only happens during my created Job and maybe what i can do about it?

    Many thanks in advance!!

    Vincent 

    Thursday, July 22, 2010 8:25 AM

Answers

  • This is most likely due to the default dateformat associated with the login that the query runs as. Try explicitly running SET DATEFORMAT at the start of the query to ensure that your dates are interpretted in the correct format no matter what.


    every day is a school day
    Thursday, July 22, 2010 8:48 AM
    Moderator

All replies

  • This is most likely due to the default dateformat associated with the login that the query runs as. Try explicitly running SET DATEFORMAT at the start of the query to ensure that your dates are interpretted in the correct format no matter what.


    every day is a school day
    Thursday, July 22, 2010 8:48 AM
    Moderator
  • Thanks for the help. This fixed the problem i was having. Everyday i learn something new this way :).
    Thursday, July 22, 2010 11:46 AM
  • If both, Datum and created are datetime, wha are you converting the created-Field?
    Thursday, July 22, 2010 11:55 AM
  • > select convert(varchar, created,101) datum, medewerker, count(*) aantal,

    Instead of 101 which is an unsafe format, use 112 which is a safe format.
    More information here: http://www.karaszi.com/SQLServer/info_datetime.asp.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, July 22, 2010 12:46 PM
  • If both, Datum and created are datetime, wha are you converting the
    created-Field?

    I assume that Vincent wants to strip the time portfion from the date.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, July 22, 2010 3:43 PM