how to do this Date function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to do this Date function

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-05 : 15:04:10

Using Date functions how to get end of every month for all months in a year?

Like

2008-01-31
2008-02-28 or 29 (According to Leap Year)
2008-03-30
.
...

...

and soon.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 15:07:18

Use this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-08-05 : 15:18:18

Not sure if it's mentioned in that thread but if you are needing to get all the records from a table with a date_field that falls on a month end you can use something like this:

select * from table
where month(date_field)<month(dateadd(dd,1,date_field))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 15:24:20

quote:
Originally posted by SCHEMA

Using Date functions how to get end of every month for all months in a year?

Like

2008-01-31
2008-02-28 or 29 (According to Leap Year)
2008-03-30
.
...

...

and soon.

This will get last day of all 12 months of "current" year:


select dateadd(day, -1, dateadd(month, m, dateadd(year, datediff(year, 0, getdate()), 0)))
from   (select m1+m2+m3+m4 m
       from    (select 0 m1 union all select 1) m1
              ,(select 0 m2 union all select 2) m2
              ,(select 0 m3 union all select 4) m3
              ,(select 0 m4 union all select 8) m4
       ) numbers
where  m between 1 and 12
order by 1

Be One with the Optimizer
TG

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-05 : 15:53:03

This is exactly what I want .TG, you are awesome.

But I want to put it as variable:
like

Declare @Endday
Set @Endday = Your query (select dateadd(day, -1, dateadd(month, m, dateadd(year, datediff(year, 0, getdate()), 0)))
from (select m1+m2+m3+m4 m
from (select 0 m1 union all select 1) m1
,(select 0 m2 union all select 2) m2
,(select 0 m3 union all select 4) m3
,(select 0 m4 union all select 8) m4
) numbers
where m between 1 and 12
order by 1)

How can i make it to work? Thanks a lot.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 16:17:41

Not sure what you mean - a variable will only hold one value. The query returns 12 values?

Be One with the Optimizer
TG

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-08-05 : 16:22:11

End of current month:
select dateadd(m, datediff(m, 0, getdate())+1, 0)-1

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 10:29:43

Suppose I have :

Declare @EndDate smalldatetime
Set @EndDate ='06/30/2008'
DECLARE @TempDate SMALLDATETIME
DECLARE @Counts TinyInt
SET @Counts = 20
SET @TempDate = @EndDate

WHILE @Counts >= 1
BEGIN
select dateadd(day, -1, dateadd(month, @Counts, dateadd(year, datediff(year, 0, @TempDate), 0)))

SET @Counts = @Counts - 1
CONTINUE
END

Why it is not looping(working) for last year as well?

It should go back for 2007 as well. Thanks in advance.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 11:25:04

Any ideas.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-06 : 11:44:15

The post I provided as your first response is one of the most useful date function available for sql. It will provide you with the functionality of basically whatever you want to do regarding dates. You should really take a look at it.

If you were to use that function the code to do what you want would simply be.

Declare @EndDate datetime,@StartDate datetime
Set @EndDate ='06/30/2008'
SET @Counts = 20
set @Startdate = dateadd(month,-1 * @Counts,@EndDate)

Select distinct END_OF_MONTH_DATE
from F_TABLE_DATE(@StartDate,@EndDate)

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 11:50:44

Thanks Vinnie.
I looked at that function .It is so huge.
I have almost got everything for 2008 but how can i modify my script so I get last year as well.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 11:53:29

[code]DECLARE @EndDate DATETIME,
@Counts TINYINT

SELECT @EndDate = '20080630',
@Counts = 20

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @EndDate) + v.Number, '18991231')
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND @Counts[/code]

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

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 11:58:27

Peso,

Thanks. I want to go backward 20 months.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 12:00:41

[code]DECLARE @EndDate DATETIME,
@Counts TINYINT

SELECT @EndDate = '20080630',
@Counts = 20

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '18991130', @EndDate) - v.Number, '18991231')
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND @Counts[/code]

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

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 12:03:44

Thanks. It is working but starting from 2008-04-30 . Actually it should start from 2008-06-30 to backward.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 12:05:20

Got it. It is -v.number + 2.

Thanks everyone.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 12:05:24

Works for me.
Have your tried to add an ORDER BY at the end of the query?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 12:06:32

You didn't copy the full new query!
If you have done so, you would ahve noticed that I changed the hardwired dates for the DATEDIFF and DATEADD functions.

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

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 12:08:12

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources