Post reply

dateadd function

  • January 22, 2008 at 1:16 am

    #185832

    Hi All,

    I need help about the syntax of the sql as below

    When I try to Execute the tsql as below I get the error message

    declare @IlkTarih datetime

    set @IlkTarih='01.01.2008'

    select dateadd(dd,1,convert(datetime,@IlkTarih,103))

    Msg 242, Level 16, State 3, Line 5

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Kind regards

  • January 22, 2008 at 1:19 am

    #770364

    I don't think points are valid date separator for style 103.

    N 56°04'39.16"
    E 12°55'05.25"

  • January 22, 2008 at 1:30 am

    #770366

    I am able to execute without any problem. I tried on sql server 2005

    See the following code

    declare @a datetime

    set @='01.01.2008'

    select dateadd(dd,1,convert(datetime,@a,103))

  • January 22, 2008 at 3:44 am

    #770404

    Seems to be a problem with the language setting.

    Easiest method to avoid it is to use ISO format for the stringdate.

    Try '20080101' instead and see what happens.

    Also, the convert(datetime, @variable, 103) is redundant, since @variable is already a datetime, the style 103 won't be applied. (and as mentioned earlier, style 103 is not dotted delimiters anyway)

    /Kenneth

  • January 22, 2008 at 3:49 am

    #770405

    Yes I can do it as well, but I suppose the problem is in first assignment. The variable is declared as datetime but then a string is assigned to it without explicit style applied. What is the value of the variable before last operation?

    ...and your only reply is slàinte mhath

  • January 22, 2008 at 4:55 am

    #770419

    As someone said above, date format #103 is NOT valid for this style of date. Check Books Online under CONVERT... the style you are looking for is style # 104.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 23, 2008 at 3:18 am

    #770813

    Hi,

    Make sure of the following settings:

    i. The Regional Settings on the Server are correct.

    ii. The SQLServer language settings match the system settings.

  • June 8, 2011 at 12:43 am

    #1335467

    make sure ur regional setting in Control Panel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply