Date Calculations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Calculations

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-05-07 : 12:27:30

If I have a date that is before the 25th I want to populate another field with the 10th of the next month. If after the 25th I want 10th of the month after.

Example:
05/20/2012
I want: 06/10/2012
05/26/2012
I want: 07/10/2012

For this example: The date I'm reading is inv_dt

Not sure if this is the right track or not:
Select case when day(inv_dt) < 25 then ????

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-05-07 : 13:23:12

I believe I have figured it out. This seems to do the trick:

select dateadd(month,1,DATEADD(DAY, -DAY(inv_dt)+10, inv_dt))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 14:08:07

quote:
Originally posted by Vack

I believe I have figured it out. This seems to do the trick:

select dateadd(month,1,DATEADD(DAY, -DAY(inv_dt)+10, inv_dt))

Does that really do what your original requirement said? For example for 5/26/2012? May be you need to enhance it a bit like this?

select dateadd(month,CASE WHEN DAY(@dt) <= 25 THEN 1 ELSE 2 END,DATEADD(DAY, -DAY(@dt)+10, @dt))

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-05-07 : 14:20:47

My problem was figuring out how to grab the invoice date and make the day the 10th. I am using the case statement to handle the other logic as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:04:27

dateadd(mm,datediff(mm,0,dateadd(dd,7,inv_dt)),10)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-05-09 : 07:19:46

Apologies for hijacking this thread, I've been cobbling together snippets of code to try to further my journey on the SQL learning curve.

I've got this.


DECLARE @DateTest AS TABLE (Datex DATE) 
DECLARE @DURATION INT
SET @DURATION = 0 
WHILE @DURATION > -1999
BEGIN
INSERT INTO @DateTest
SELECT DATEADD (DAY, DATEDIFF(DAY, 0, GETDATE()) + @DURATION, 0) AS Datex
SET @DURATION = @DURATION - 1
END

SELECT CONVERT (VARCHAR, Datex, 103) AS DATE 
,DAY (Datex) AS DAYNO
,MONTH (Datex) AS MONTHNO
,YEAR (Datex) AS YEARNO
,STR (DATEPART (YEAR, Datex), 4, 0) + REPLACE (STR (DATEPART (q, Datex), 2, 0), ' ', '0') AS QUARTERNO
,DATEADD (MONTH, CASE WHEN DAY (Datex) <= 25 THEN 1 ELSE 2 END, DATEADD (DAY, -DAY (Datex) +10, Datex)) AS INVDATE

FROM @DateTest

How do I get the red code to display as dd/mm/yyyy? Currently it's yyyy-mm-dd.

TIA as always

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-09 : 08:22:01

something like

CONVERT(datetime,DATEADD (MONTH, CASE WHEN DAY (Datex) <= 25 THEN 1 ELSE 2 END, DATEADD (DAY, -DAY (Datex) +10, Datex)),103)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 16:05:18

question is why should you do this in t-sql
it sounds like a presentation issue so why cant you handle it at front end application using formatting functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-10 : 07:04:34

quote:
Originally posted by visakh16

question is why should you do this in t-sql
it sounds like a presentation issue so why cant you handle it at front end application using formatting functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I stopped asking this question because the answer is always "Because i was told to do it this way", or it is homework or some such.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 12:01:23

quote:
Originally posted by DonAtWork

quote:
Originally posted by visakh16

question is why should you do this in t-sql
it sounds like a presentation issue so why cant you handle it at front end application using formatting functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I stopped asking this question because the answer is always "Because i was told to do it this way", or it is homework or some such.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I also realise that but couldnt help myself from asking

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-05-12 : 14:32:23

quote:
Originally posted by visakh16

quote:
Originally posted by DonAtWork

quote:
Originally posted by visakh16

question is why should you do this in t-sql
it sounds like a presentation issue so why cant you handle it at front end application using formatting functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I stopped asking this question because the answer is always "Because i was told to do it this way", or it is homework or some such.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I also realise that but couldnt help myself from asking

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, as I said in my original post, I'm learning (or trying to learn) SQL and this was just basically a play around with code to see how and why it worked

Oh, and DonAtWork's code didn't work

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-12 : 15:49:10

Make the change shown in red to Don's code:

CONVERT(CHAR(10),DATEADD (MONTH, CASE WHEN DAY (Datex) <= 25 THEN 1 ELSE 2 END, DATEADD (DAY, -DAY (Datex) +10, Datex)),103)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 19:32:01

quote:
Originally posted by Rasta Pickles

quote:
Originally posted by visakh16

quote:
Originally posted by DonAtWork

quote:
Originally posted by visakh16

question is why should you do this in t-sql
it sounds like a presentation issue so why cant you handle it at front end application using formatting functions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I stopped asking this question because the answer is always "Because i was told to do it this way", or it is homework or some such.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I also realise that but couldnt help myself from asking

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, as I said in my original post, I'm learning (or trying to learn) SQL and this was just basically a play around with code to see how and why it worked

Oh, and DonAtWork's code didn't work

then as suggested you can try impleenting this at front end using formatting function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-13 : 03:02:31

[code]DECLARE @Sample TABLE
(
OriginalDate DATE,
WantedDate DATE
)

INSERT @Sample
VALUES ('20120520', '20120610'),
('20120526', '20120710')

-- SwePeso
SELECT OriginalDate,
WantedDate,
DATEADD(MONTH, DATEDIFF(MONTH, '18991231', DATEADD(DAY, 7, OriginalDate)), '19000110')
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-05-13 : 11:39:09

quote:
Originally posted by visakh16
then as suggested you can try impleenting this at front end using formatting function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Indeed and I thank you for your optimism.

When my SQL abilities are akin to the experts on here then I will, of course, follow your quite excellent advice.

But I'm at the bottom of the learning curve at the moment, somewhere every single one of you were at some point in the dim and distant past.

But with every new day there's hope.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:06:37

quote:
Originally posted by Rasta Pickles

quote:
Originally posted by visakh16
then as suggested you can try impleenting this at front end using formatting function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Indeed and I thank you for your optimism.

When my SQL abilities are akin to the experts on here then I will, of course, follow your quite excellent advice.

But I'm at the bottom of the learning curve at the moment, somewhere every single one of you were at some point in the dim and distant past.

But with every new day there's hope.

My point was not reg your SQL abilities but refering to best way of doing this.
Doing this in sql requires converting it to charater type which will cause issues if you're using the converted value for further manipulations like sorting. Thats why its recommended to do this at front end if at all possible.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources