Moving Mnth Dates - 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

Moving Mnth Dates

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-28 : 20:53:15

Hi,

I have created a formula that moves week dates (4 Weeks)(For header purposes). I want this to be applied in months. Is it possible?

Here's my formula:

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 1 AS Week4,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 15 AS Week3,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 29 AS Week2,DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 14 * 14, '19000107') - 43 AS Week1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 03:22:38

Yes.

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 04:32:55

How is it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 04:36:06

Oh no. You get one guess first.
If you want to change every two weeks (14 days) to one month (1 month), what possibly could you change in your original code to accomplish that?

Which by the way were suggested to you here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102444

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 04:43:25

change it to 28?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 04:46:47

Almost. It's true that all months have 28 days but most of them has more than 28 days.

SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') AS Month4,
	DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS Month3,
	DATEADD(MONTH, DATEDIFF(MONTH, '19000301', GETDATE()), '19000101') AS Month2,
	DATEADD(MONTH, DATEDIFF(MONTH, '19000401', GETDATE()), '19000101') AS Month1

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 04:54:10

ok but i want it in month. How's that?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 05:02:43

I got it. Thanks. Can I change the date format to May - 08? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 05:15:12

Use the CONVERT function to do this.
Surely you must have learned to use Books Online by now?

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 05:31:10

how's that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 05:50:11

Which one of the sentences didn't you understand?

E 12°55'05.25"
N 56°04'39.16"

SQLNewbieHere
Starting Member

12 Posts

Posted - 2008-05-29 : 06:54:29

how can i convert it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 07:32:33

Using the CONVERT function.

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 07:40:20

Okay I need to check on that. Just one inquiry. Same concept as this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') AS Month4,
DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS Month3,
DATEADD(MONTH, DATEDIFF(MONTH, '19000301', GETDATE()), '19000101') AS Month2,
DATEADD(MONTH, DATEDIFF(MONTH, '19000401', GETDATE()), '19000101') AS Month1

Is there a way to make it quarterly? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 08:11:19

Of course.

How did you go from 14 days to 1 month?
Please OPEN BOOKS ONLINE and read about DATEADD and DATEDIFF function and how they work.
If you do, you will very easy spot how to do for QUARTERS.

E 12°55'05.25"
N 56°04'39.16"

SQLNewbieHere
Starting Member

12 Posts

Posted - 2008-05-29 : 08:16:54

Yes, however, they dont' have the equal days.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 08:29:28

Yes, I got the same issue.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 08:30:53

I'm getting different results with this: SELECT DATEADD(MONTH, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101') AS Month4

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-29 : 10:17:18

quote:
Originally posted by Peso

Yes.
E 12°55'05.25"
N 56°04'39.16"

quote:
Originally posted by Peso

Oh no. You get one guess first.
If you want to change every two weeks (14 days) to one month (1 month), what possibly could you change in your original code to accomplish that?

Priceless responses.

Terry

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 14:13:40

quote:
Originally posted by cutiebo2t

I'm getting different results with this: SELECT DATEADD(MONTH, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101') AS Month4

Why don't you want QUARTERS all over? Why month one place and quarter one place?

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-29 : 18:57:39

How's that?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources