I need to take two fields and make a Date field

Question

I need to take two fields and make a Date field

randyetheridge

SSCommitted

Points: 1703
More actions
January 15, 2020 at 2:49 pm

#3715643

my table is as follows
YearID 2019
PeriodIDValue P12
PeriodID December
I need to create a single field 12/31/2019
of course if the values are 2020, P1, January I need 1/31/2020
or if 2019,P5, May I need 5/31/2019 etc.
I cannot figure out how to do this.
I was trying Case When
i.e.
CASE
WHEN PeriodIDValue = 'P1' THEN '1/31'&YearID
WHEN PeriodIdValue = 'P2' THEN '2/28'&YearID
etc
Else '12/31/2019' END AS DateID
but I was getting a conversion failure on varchar and of course every 4 years the code would be wrong, and it just seemed to brute force
any help is appreciated.

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

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Something like the below?

DECLARE @TestTable TABLE (YearID INT, PeriodIDValue VARCHAR(3), PeriodID VARCHAR(10))
INSERT INTO @TestTable VALUES 
(2019,'P12','December'),
(2019,'P5','May'),
(2020,'P1','January')

SELECT *,
--THIS DATE ADD GOES BACK TO THE LAST DAY OF THE MONTH
dateadd(day,-1,
--THIS DATE ADD GOES TO THE START OF THE NEXT MONTH
dateadd(mm, datediff(mm, 0, 
--DATEFROMPARTS HERE TO BUILD THE 1ST OF THE MONTH FROM THE SAMPLE DATA
DATEFROMPARTS (YearID,replace(PeriodIDValue,'P',''),1)
--THEN ADD A MONTH
) + 1, 0)
--THEN REMOVE A DAY
)
FROM @TestTable

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

ok basically that worked like a charm. but the answer was 2019-12-31 00:00:00.000

I need 12/31/2019

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

If it's for storing in a table, don't worry - it'll get stored the same way no matter how it's presented to you. If it's for you to see in a result set, either have your front end format it for you, or use CONVERT with the appropriate date style.

John

randyetheridge SSCommitted Points: 1703 More actions · Answer 4

ok, so this is a view, that is used to send a TXT file to a third party application. so I need to use CONVERT. I will need to research. thanks

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

+1 for what John said, formatting like that should be done in the application layer.

Let SQL store it and present it as a native SQL datatype, dates should be handled as dates, not strings and should be handled in an ISO way so that all operations on them are universal.

But the below will convert it to style 101 for the US mm/dd/yyyy format you need

DECLARE @TestTable TABLE (YearID INT, PeriodIDValue VARCHAR(3), PeriodID VARCHAR(10))
INSERT INTO @TestTable VALUES
(2019,'P12','December'),
(2019,'P5','May'),
(2020,'P1','January')
SELECT *,
CONVERT(VARCHAR(10),
--THIS DATE ADD GOES BACK TO THE LAST DAY OF THE MONTH
dateadd(day,-1,
--THIS DATE ADD GOES TO THE START OF THE NEXT MONTH
dateadd(mm, datediff(mm, 0,
--DATEFROMPARTS HERE TO BUILD THE 1ST OF THE MONTH FROM THE SAMPLE DATA
DATEFROMPARTS (YearID,replace(PeriodIDValue,'P',''),1)
--THEN ADD A MONTH
) + 1, 0)
--THEN REMOVE A DAY
)
,101)
FROM @TestTable

randyetheridge SSCommitted Points: 1703 More actions · Answer 6

ok so got that,

Convert(CHAR(10),DATEADD(day,-1,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(YearID,REPLACE(PeriodIDValue,'P',''),1))+1,0)),101) AS PeriodEndDate

However I just realized/remembered my test server is on 2012, but my production server will not be on 2012(I.e. still on 2008) until Feb 24, 2020.

in 2008 DATEFROMPARTS is not valid

is there any replacement for DATEFROMPARTS that would work in 2008?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yes, you just need a couple more DATEADDs. Start with a base date, say 1999-12-31, and add the appropriate number of years, then the appropriate number of months.

John

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 8

Here is an idea:

Declare @yearID int = 2019
      , @periodIDValue varchar(3) = 'P12';

 Select dateadd(day, -1, dateadd(month, 1, cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8))))

For 2012 and above - you can return a DATE data type with these:

 Select eomonth(cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8)))
      , eomonth(datefromparts(@yearID, replace(@periodIDValue, 'P', ''), 1))

And if you need it as a datetime:

 Select cast(eomonth(cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8))) As datetime)
      , cast(eomonth(datefromparts(@yearID, replace(@periodIDValue, 'P', ''), 1)) As datetime)

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

randyetheridge SSCommitted Points: 1703 More actions · Answer 9

thanks that worked in both 2008 and 2012. code is updated and users are happy