Converting to last minute of a month

Question

Post reply

Converting to last minute of a month

duggal.priyanka06

Say Hey Kid

Points: 684
More actions
December 13, 2011 at 3:44 am

#248103

Hi,
I have the following query where @User::Cob_Dt is a variable giving me the previous business day.The
following gives me day of a month i.e. 2011-12-31 00:00:00.000
declare @cob_dt as datetime
select @cob_dt='2011-12-12'
select DATEADD(dd,-DAY(DATEADD("mm",1,@cob_dt)),DATEADD("mm",1,@cob_dt))
I want it to be last day and last min and last sec of a month. I need to use this in SSIS derived column.

Viewing 9 posts - 1 through 8 (of 8 total)

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

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 1

hi will you make little be clear to you query with an example what actually you want.. i am little bit confuse

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

duggal.priyanka06 (12/13/2011)
Hi,
I have the following query where @User::Cob_Dt is a variable giving me the previous business day.The
following gives me day of a month i.e. 2011-12-31 00:00:00.000
declare @cob_dt as datetime
select @cob_dt='2011-12-12'
select DATEADD(dd,-DAY(DATEADD("mm",1,@cob_dt)),DATEADD("mm",1,@cob_dt))
I want it to be last day and last min and last sec of a month. I need to use this in SSIS derived column.

Select the first day of the next month, and substract one second.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

duggal.priyanka06 (12/13/2011)
Hi,
I have the following query where @User::Cob_Dt is a variable giving me the previous business day.The
following gives me day of a month i.e. 2011-12-31 00:00:00.000
declare @cob_dt as datetime
select @cob_dt='2011-12-12'
select DATEADD(dd,-DAY(DATEADD("mm",1,@cob_dt)),DATEADD("mm",1,@cob_dt))
I want it to be last day and last min and last sec of a month. I need to use this in SSIS derived column.

It's generally much easier to compare against the first day of the month (with no time element) using ">=" or "<=" than to compare against "last day and last min and last sec of a month" using ">" or "<".

When you say you want the last day, can you confirm you mean the last day of the same month/year as cob_dt?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Quick today, Koen :hehe:

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

ChrisM@home (12/13/2011)
Quick today, Koen :hehe:

I do my best 🙂

But you make an excellent point as well. Using the DATE datatype allows you to write easier queries with BETWEEN and you don't need to take into account those pesky time portions 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

duggal.priyanka06 Say Hey Kid Points: 684 More actions · Answer 6

This works..Subtract 1 second from first date of next month

declare @cob_dt as datetime

select @cob_dt='2012-2-12'

select DATEADD("s",-1,DATEADD("dd",-(DAY(DATEADD("mm",1,@cob_dt))-1),DATEADD("mm",1,@cob_dt)))

Thanks all

david.kratz SSC Eights! Points: 978 More actions · Answer 7

A solution I often use is

Instead of saying date <= last-second-of-previous-month

I say

date < trunc(sysdate,'MM').

Of course this is out of the question if using Between.

In that case the afore-mentioned method of subtracting one second from trunc(sysdate,'MM')

would work if you know what fraction of a day a second is ...

trunc(sysdate.'MM')-.00001 ???

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

david.kratz (12/29/2011)
A solution I often use is
Instead of saying date <= last-second-of-previous-month
I say
date < trunc(sysdate,'MM').
Of course this is out of the question if using Between.
In that case the afore-mentioned method of subtracting one second from trunc(sysdate,'MM')
would work if you know what fraction of a day a second is ...
trunc(sysdate.'MM')-.00001 ???

I don't remember if TRUNC function was available in SQL Server (now it is in Denali).

TRUNC Function

http://msdn.microsoft.com/en-us/library/ee634907(v=sql.110).aspx