SQL Server Performance

set dateformat to dd-mm-yyyy problem

Discussion in 'General Developer Questions' started by d17may, May 30, 2006.

  1. d17may New Member

    i write procedure which receives date in format dd-mm-yyyy
    mm can be 1 or 2 digit similar with date e.g.
    1-1-2006 ,12-28-2005
    how i set the dateformat
    i write set dateformat dmy it sometimes give error
    Thanks
  2. FrankKalis Moderator

  3. d17may New Member

  4. khtan New Member

    how do you conver to datetime datatype. Can you post your code ?


    KH
  5. FrankKalis Moderator


    SET DATEFORMAT dmy
    SELECT CAST('1-1-2006' AS DATETIME)


    ------------------------------------------------------
    2006-01-01 00:00:00.000

    (1 row(s) affected)

    works as it should. So I guess the procedure gets invalid data passed such as


    SET DATEFORMAT dmy
    SELECT CAST('1-13-2006' AS DATETIME)


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


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  6. Adriaan New Member

    When feeding dates in strings, use the 'yyyy-mm-dd' format only - will always be interpreted correctly by SQL Server.

    The only other accepted format is the Windows date format on the server on which SQL Server is running. This is hard to see from the outside, so don't bother and just use 'yyyy-mm-dd'.
  7. d17may New Member

    quote:Originally posted by khtan

    how do you conver to datetime datatype. Can you post your code ?


    KH
    My code is
    ...................
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO


    ALTER procedure SPScheduleVehicle
    @vehicle_id nvarchar(30),
    @trip_id uniqueidentifier,
    @from_date datetime,
    @to_date datetime
    As
    Begin
    set dateformat dmy
    if not exists (select vehicle_id from schedule_details
    where vehicle_id=@vehicle_id)
    Begin
    insert into schedule_details(trip_id,vehicle_id,from_date,to_date)
    values(@trip_id,@vehicle_id,@from_date,@to_date)
    End
    Else
    Begin
    update schedule_details
    set trip_id=@trip_id,
    from_date=@from_date,
    to_date=@to_date
    where vehicle_id=@vehicle_id
    End
    End


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    .................................
    if i pass date as 1-1-2006 it works fine
    but when i give 30-12-2006 it gives error
  8. khtan New Member

    use
    select convert(datetime, '30-12-2006', 103)

    your problem is the input parameter @from_date & @to_date is datetime format

    so when you called the sp
    exec SPScheduleVehicle 'whatever', 1, '30-12-2006'
    it will treat the 30-12-2006 in MM-DD-YYYY format. Which means the set dateformat dmy has no effect at all.

    Pass the datetime in YYYYMMDD format.
    exec SPScheduleVehicle 'whatever', 1, '20061230'





    KH
  9. khtan New Member

    "if i pass date as 1-1-2006 it works fine
    but when i give 30-12-2006 it gives error"

    The convertion error is not within the SP but when converting the datetime in string to datetime datatype.


    KH
  10. d17may New Member

    Thanks everyone for taking time
    When i change datatype of input parameter from datetime to nvarchar
    it not gives any error

Share This Page