Problems with date formats

  • I am creating a User Defined Function in which I pass in a character string representing a date and I want to add a number of days to it. The calling application controls the date format passed in - 2015-11-01 12:24:32 (I'm not actually interested in the time component)

    I tried this

    SELECT DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120))

    Which does what I want and gives me

    2015-11-08 12:24:32.000

    However the function must return just the date component. When I wrap the result in a substring

    SELECT Substring(DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)),1,10)

    I get an error - Argument data type datetime is invalid for argument 1 of substring function. Because , I assume it needs to be character data to substring

    So I CAST it

    SELECT Substring(CAST(DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)) AS VARCHAR),1,11)

    And it works but the format is no longer as I need it

    Nov 8 2015

    What am I missing????

    The input format is fixed and I need to add 7 days and the UDF must return a string in the format 2021-11-08. I have tried a number of different third arguments in the convert function. Anyone point me in the right direction

    Thanks

  • Change the date format in the convert from 120 to 23.

     

  • Here are a couple of ways to do it.  Like aadcock says above, format 23 comes into play.  Be warned though... in a lot of languages, that format (23) will produce the desired YYYY-MM-DD format BUT will produce the non-ISO format of YYYY-DD-MM in some other languages like French (for example) WHEN DATETIME IS USED (no problem with DATE datatype).

        SET LANGUAGE English;
    SELECT OK = DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')) --Return as a DATE datatype
    ,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
    ,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATETIME,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
    ;
    SET LANGUAGE French;
    SELECT OK = DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')) --Return as a DATE datatype
    ,OK = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATE,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, no language issues
    ,Whoops = CONVERT(CHAR(10),DATEADD(dd,7,CONVERT(DATETIME,'2015-11-01 12:24:32')),23) --Return as a CHAR(10) datatype, has language issues
    ;

    Results of above code:

    --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)

  • Many thanks for your help.  I love forums!

    Cheers

  • just use straight convert with the correct destination type (e.g. no VARCHAR without specifying a size - bad practice!!) and with the correct format - no need for substring either.

     

    SELECT convert(varchar(10), DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)), 120)

    format 120 - ODBC canonical - yyyy-mm-dd hh:mi:ss (24h)

    from docs here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

     

  • frederico_fonseca wrote:

    just use straight convert with the correct destination type (e.g. no VARCHAR without specifying a size - bad practice!!) and with the correct format - no need for substring either.

    SELECT convert(varchar(10), DATEADD(day, 7, convert(datetime,'2015-11-01 12:24:32',120)), 120)

    format 120 - ODBC canonical - yyyy-mm-dd hh:mi:ss (24h)

    from docs here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

    Since the return will always be 10 characters - never less and never more - I would use char(10).

    With that said, I would not do this - I would cast/convert to a date data type and return that.  Let the client convert to a string if that is what is needed, but returning a string from a string after converting to a datetime just rubs me the wrong way.

    If you have to - here is another option:

    cast(dateadd(day, 7, cast('2015-11-01 12:24:32' as date)) as char(10))

    This should not be impacted by language settings - since cast/convert to date recognizes ISO format.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Select the dates whose format your want to change, or empty cells where you want to insert dates.

    Press Ctrl+1 to open the Format Cells dialog. ...

    In the Format Cells window, switch to the Number tab, and select Date in the Category list.

    Under Type, pick a desired date format.

     

    Target Card Balance Now

    • This reply was modified 2 years, 5 months ago by  Carmon28.

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

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