Display on Month and Year from date formula ??

Question

Display on Month and Year from date formula ??

jbalbo

SSCertifiable

Points: 7583
More actions
February 15, 2013 at 10:47 am

#270971

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

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Thanks Sean

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

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)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

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/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 10

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 12

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

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/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 14

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