Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Display on Month and Year from date formula ?? Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 10:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi,

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

Thanks
Post #1420681
Posted Friday, February 15, 2013 10:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 13,082, Visits: 12,544
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1420683
Posted Friday, February 15, 2013 11:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Thanks Sean
Great Idea, someone exlse mentioned the simple math problem, now I understand
Post #1420696
Posted Friday, February 15, 2013 3:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 35,350, Visits: 31,891
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1420811
Posted Friday, February 15, 2013 3:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1420815
Posted Friday, February 15, 2013 4:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 35,350, Visits: 31,891
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1420821
Posted Monday, February 18, 2013 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
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/
Post #1421109
Posted Monday, February 18, 2013 12:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 35,350, Visits: 31,891
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1421327
Posted Monday, February 18, 2013 2:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 2,194, Visits: 3,302
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1421349
Posted Monday, February 18, 2013 4:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 35,350, Visits: 31,891
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. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1421370
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse