Display on Month and Year from date formula ??

  • Hi,

    I want to display only MM/YY from the formula of @enddate -21??

    Thanks

  • jbalbo (2/15/2013)


    Hi,

    I want to display only MM/YY from the formula of @enddate -21??

    Thanks

    This type of display should be handled in the front end. SQL is not good at string manipulation like for presentation. Also, you should not use simple math with datetime data like this. You should use DATEADD because the simple math does not work with date or datetime2 datatypes.

    If you are deadset on using a t-sql hammer to force sql to do your presentation you can do something like this.

    declare @enddate datetime = getdate()

    select dateadd(day, -21, @enddate),

    right('0' + cast(month(dateadd(day, -21, @enddate)) as varchar(2)), 2) + '/' + cast(year(dateadd(day, -21, @enddate)) as varchar(4))

    Please note that this is not my recommendation.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Sean

    Great Idea, someone exlse mentioned the simple math problem, now I understand

  • Sean Lange (2/15/2013)


    You should use DATEADD because the simple math does not work with date or datetime2 datatypes.

    I have to ask... if you're not using the DATE or DATETIME2 datatypes, why does that matter?

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

  • Jeff Moden (2/15/2013)


    Sean Lange (2/15/2013)


    You should use DATEADD because the simple math does not work with date or datetime2 datatypes.

    I have to ask... if you're not using the DATE or DATETIME2 datatypes, why does that matter?

    Consistency? Personally, I don't use the @DataVar + NumDays to add NumDays to a datetime value. I like using the DATEADD function, it provides clarity.

  • I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.

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

  • Jeff Moden (2/15/2013)


    I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.

    My version

    DECLARE@date DATETIME

    SET@date = CURRENT_TIMESTAMP

    SELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added

    I get the output as below

    Date Date_Added

    2013-02-18 10:29:50.343 2013-02-20 04:11:31.383

    The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.

    Is there any other strange thing?


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (2/18/2013)


    Jeff Moden (2/15/2013)


    I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.

    My version

    DECLARE@date DATETIME

    SET@date = CURRENT_TIMESTAMP

    SELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added

    I get the output as below

    Date Date_Added

    2013-02-18 10:29:50.343 2013-02-20 04:11:31.383

    The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.

    Is there any other strange thing?

    I'll try to get back to this after worrk tonight. In the mean time, remember that DATETIME has an accuracty of only 3.3 milliseconds. All DATETIMES will end with 0, 3, or 7 for the final digit in the milliseconds.

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

  • declare @enddate datetime

    set @enddate = getdate()

    SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (2/18/2013)


    declare @enddate datetime

    set @enddate = getdate()

    SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')

    Except for Sean, we did forget to answer the original question. :blush: Thanks, Scott.

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

  • Jeff Moden (2/18/2013)


    ScottPletcher (2/18/2013)


    declare @enddate datetime

    set @enddate = getdate()

    SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')

    Except for Sean, we did forget to answer the original question. :blush: Thanks, Scott.

    Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (2/18/2013)


    Jeff Moden (2/18/2013)


    ScottPletcher (2/18/2013)


    declare @enddate datetime

    set @enddate = getdate()

    SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')

    Except for Sean, we did forget to answer the original question. :blush: Thanks, Scott.

    Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy.

    It was and my comment was meant to be a compliment to you.

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

  • Jeff Moden (2/19/2013)


    ScottPletcher (2/18/2013)


    Jeff Moden (2/18/2013)


    ScottPletcher (2/18/2013)


    declare @enddate datetime

    set @enddate = getdate()

    SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')

    Except for Sean, we did forget to answer the original question. :blush: Thanks, Scott.

    Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy.

    It was and my comment was meant to be a compliment to you.

    DOH :blush::blush:

    So sorry, I misread it.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Jeff Moden (2/15/2013)


    Sean Lange (2/15/2013)


    You should use DATEADD because the simple math does not work with date or datetime2 datatypes.

    I have to ask... if you're not using the DATE or DATETIME2 datatypes, why does that matter?

    I would say it is the same type of reasoning behind using proper date formats. If you use dateadd it will continue to work even if somebody decides they need to change the datatype to datetime2 because they need more accuracy. Much like anything else we do, if there is a simple solution that makes your code more robust and you know about it why would you code the shortcut that might not function in the future?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Kingston Dhasian (2/18/2013)


    Jeff Moden (2/15/2013)


    I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.

    My version

    DECLARE@date DATETIME

    SET@date = CURRENT_TIMESTAMP

    SELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added

    I get the output as below

    Date Date_Added

    2013-02-18 10:29:50.343 2013-02-20 04:11:31.383

    The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.

    Is there any other strange thing?

    How about this?

    DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'

    SELECT CAST(GETDATE() AS DATETIME2)

    ,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT)

    ,DATEADD(millisecond

    ,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME))

    ,CAST(GETDATE() AS DATETIME2))))

    So Jeff, now that I've had my fun, how about showing us how you would do it?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 23 total)

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