I need help getting last day of prior month in a view statement

Question

I need help getting last day of prior month in a view statement

randyetheridge

SSCommitted

Points: 1703
More actions
June 1, 2018 at 9:59 am

#369049

so this is my code

CONVERT(DATE,DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0,GETDATE()) , 0)))
I run this today and it returns 2018-05-31
this is the correct date I need
however this is a view that will be sent as a TXT file to a third party. they require 05/31/2018. I cannot seem to find the proper code to achieve this. Any help is appreciated

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Is that expression in your WHERE or SELECT? Dates don't have any kind of stored format, so using a WHERE clause with the format MM/dd/yyyy could yield very odd results, or conversion error.

If it's in your SELECT, idealy, you do that formatting change in your presentation layer, not in your SQL. You can, however, change the formatting my converting the value to a varchar and using a style code. So, for example with GETDATE(), the expression would be:
CONVERT(varchar(10),CONVERT(date,GETDATE()),101)

Don't do this in your WHERE though.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

select statement
SELECT Entity AS HFMEntity, Account, '' AS Group1, '' AS Group2, '' AS Group3, '' AS Group4, '' AS Key7, '' AS Key8, '' AS Key9, '' AS Key10, '' AS AcctDesc, '' AS AcctRef,
                  'C' AS FinancialStatement, '' AS AcctType, 'TRUE' AS ActiveAcct, 'TRUE' AS ActivityInPeriod, '' AS AlternateCurrency, 'USD' AS AcctCurrency, CONVERT(DATE, DATEADD(MS,
                  - 3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))) AS PeriodEndDate, CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [USAmt] * - 1 ELSE [USAMT] END AS [Amt(USD)], '' AS [Amt(Alt)], CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [Amt] * - 1 ELSE [AMT] END AS [Amt(Local)]
FROM     dbo.CIMBalances2

so this is the output

and I need the field PeriodEndDate to be 05/31/2018

amy26 SSCrazy Eights Points: 8926 More actions · Answer 3

amy26

SSCrazy Eights

Points: 8926

June 2, 2018 at 8:15 pm

#1992749

.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

randyetheridge - Friday, June 1, 2018 11:20 AM
select statement
SELECT Entity AS HFMEntity, Account, '' AS Group1, '' AS Group2, '' AS Group3, '' AS Group4, '' AS Key7, '' AS Key8, '' AS Key9, '' AS Key10, '' AS AcctDesc, '' AS AcctRef,
                  'C' AS FinancialStatement, '' AS AcctType, 'TRUE' AS ActiveAcct, 'TRUE' AS ActivityInPeriod, '' AS AlternateCurrency, 'USD' AS AcctCurrency, CONVERT(DATE, DATEADD(MS,
                  - 3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))) AS PeriodEndDate, CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [USAmt] * - 1 ELSE [USAMT] END AS [Amt(USD)], '' AS [Amt(Alt)], CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [Amt] * - 1 ELSE [AMT] END AS [Amt(Local)]
FROM     dbo.CIMBalances2
so this is the output
and I need the field PeriodEndDate to be 05/31/2018

So how do you plan to present this data to the people who will consume it? If you paste it into Excel, it will magically appear in the correct format because of the way Excel works with date data. If you place it into an SSRS report, then you can format the text box that the column ends up in to use the right date format. Of course, using the CONVERT function with a style number of 101 will also accomplish the result, but why waste the database effort on it if another tool will be involved anyway, as you may not need to do anything at all (Excel).

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

randyetheridge - Friday, June 1, 2018 11:20 AM
select statement
SELECT Entity AS HFMEntity, Account, '' AS Group1, '' AS Group2, '' AS Group3, '' AS Group4, '' AS Key7, '' AS Key8, '' AS Key9, '' AS Key10, '' AS AcctDesc, '' AS AcctRef,
                  'C' AS FinancialStatement, '' AS AcctType, 'TRUE' AS ActiveAcct, 'TRUE' AS ActivityInPeriod, '' AS AlternateCurrency, 'USD' AS AcctCurrency, CONVERT(DATE, DATEADD(MS,
                  - 3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))) AS PeriodEndDate, CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [USAmt] * - 1 ELSE [USAMT] END AS [Amt(USD)], '' AS [Amt(Alt)], CASE WHEN Substring([account], 5, 1) BETWEEN '2' AND
                  '4' THEN [Amt] * - 1 ELSE [AMT] END AS [Amt(Local)]
FROM     dbo.CIMBalances2
so this is the output
and I need the field PeriodEndDate to be 05/31/2018

The formula you are probably looking for to create the display version of the date for the text file is this:

SELECT CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)),101)

randyetheridge SSCommitted Points: 1703 More actions · Answer 6

that is perfect, just what I needed. thank you so much.