CODEWITHSUNDEEP
sqlsql-server-2005
Jamie S
Jamie S

Reputation: 75

Date calculation in variable

I'm doing my best to set a date variable so I can compare it later. I would like something that basically says:

If the current day-of-month is less than 11, then date is 10th of LAST month
If the current day-of-month is greater-than-or-equal-to 11, then date is 10th of THIS month

Date is 11/6/2012 expected output:

@PODate = 10/10/2012

Date is 11/16/2012 expected output:

@PODate = 11/10/2012

Currently all I have is this:

DECLARE @PODate as DATETIME
Set @PODate = Convert(varchar(8),GetDate(),1)

Any tips or help would be greatly appreciated. Thank you!!

Upvotes: 1

Views: 223

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Trying to keep it as straightforward as possible:

declare @PODate datetime
select @PODate = DATEADD(month,
   DATEDIFF(month,'20010110',CURRENT_TIMESTAMP) +
      CASE WHEN DATEPART(day,CURRENT_TIMESTAMP) <= 10 THEN -1 ELSE 0 END,
   '20010110')

The surrounding DATEADD/DATEDIFF pair are being used to normalize the date to the 10th of the current month. We then use a small CASE expression to subtract a month if the day is less than or equal to the 10th.

Whatever solution you pick, please try to avoid ones that do it as string manipulation. The usual cause of datetime related bugs in SQL is when people treat dates as strings. Keeping the data types appropriately is usually the best way to prevent these issues.

There are, admittedly, 2 strings in my solution, but these are fixed constant strings (all that matters is that they're both for the same year and month, and the second one is for the 10th of the month) and are in an unambiguous format.

Upvotes: 2

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

DECLARE @PODate date = '20121116'
SELECT CASE WHEN DATEPART(day, @PODate) < 11 THEN DATEADD(mm, DATEPART(mm, GETDATE()) - DATEPART(mm, @PODate) - 1, DATEADD(day, 10 - DATEPART(day, @PODate), @PODate))
                                             ELSE DATEADD(mm, DATEPART(mm, GETDATE()) - DATEPART(mm, @PODate), DATEADD(day, 10 - DATEPART(day, @PODate), @PODate)) END

Demo on SQLFiddle

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

Try this: SQL Fiddle

DECLARE 
    @PODate as DATETIME,
    @LastMonth as DateTime,
    @strDate as Varchar(50)

set @PODate = '11/16/2012'
set @LastMonth = DATEADD(MONTH, -1, @PODate)


if(DAY(@PODate) < 11)
   SET @strDate = CAST(MONTH(@LastMonth) AS VARCHAR)+'/10/'+CAST(YEAR(@LastMonth) AS VARCHAR)
else
   SET @strDate = CAST(MONTH(@PODate) AS VARCHAR)+'/10/'+CAST(YEAR(@PODate) AS VARCHAR)

Select CAST(@strDate AS DateTime)

Upvotes: 1

Amir
Amir

Reputation: 9627

DECLARE @currDate DATE = dbo.GetDate()
DECLARE @day INT =  day(@currDate)
DECLARE @month INT 
DECLARE @year INT

DECLARE @PODate DATE

IF( @day >= 11)
BEGIN
    SET @month = month(@currDate)
    SET @year = year(@currDate)
END 
ELSE BEGIN
    SET @month =  month(dateadd(m,-1,@currDate))
    SET @year = year(dateadd(m,-1,@currDate))       
END

SET @PODate = convert(DATE,'10-' + convert(VARCHAR,@month) + '-' + convert(VARCHAR,@year))

PRINT    @PODate

if @currDate = '11-jan-2013' , @PODate will be '10-jan-2013', and if @currDate = '07-jan-2013' , @PODate will be '10-Dec-2012'

Upvotes: 0

Related Questions