SQLTeam.com | Weblogs | Forums

Help with Computed Column involving Dates


#1

I was hoping I could get some help with this Computed column. I've given up trying to get it to work. It works in most cases but not all. It's very random when it works and when it does not.
There are 2 fields involved:
YR - a 4 character string containing a year, e.g. "2015"
PDate - a Date, e.g. 2014-10-03

I want to create a computed field where the Month and Day come from PDate, and the Year is YR when the Month is 1 through 9 and it's YR -1 when the month is 10-12.
That's it. Here's what I have, which seems to work if I do it in a Select statement but does not always work if I do it as a Computed column. When I say it fails randomly, I mean that with the same values in YR and PDate, sometimes it works and sometimes it comes up with some completely random date.

(case when datepart(month,[PDate])<(10)
then CONVERT(varchar,[YR],0)
else CONVERT(varchar,[YR]-(1),0)
end

  • right([PDate],(6)))

#2

Can you please post the rows which case the apparently random results?

Also, is PDate a datetime, date, datetime2, or what data type?

I can see some implicit conversions, which you should watch out for.

CONVERT(varchar,[YR] -1 ,0)

first implicitly converts [YR] to integer. It's better to be explicit.


#3

Note this:

The following table lists the styles for which the string-to-datetime conversion is nondeterministic.
All styles below 100 *1
106
107
109
113
130
*1 With the exception of styles 20 and 21

You're using 0, which is nondeterministic according to the above.

I'd use CAST(YR as int) instead


#4

The things you are mentioning are probably the problems. I'll take a look at them.

For example, 2 records will look like this:

2016 2014-10-19 correctly produces 2015-10-19
and then the next record will be this:
2016 2014-10-19 produces 2016-06-02
!!!!


#5

I strongly advise avoiding using string conversion to manipulate dates, the CPU effort involved is much greater than using just arithmetic

Also, using VARCHAR without any size parameter is risky - SQL will at times give you varchar(30) and at other times varchar(1)

How about:

SELECT	[YR], [PDate],
	-- SQL 2012:
	DATEFROMPARTS(CASE WHEN DATEPART(month, [PDate]) < 10
			THEN [YR]
			ELSE [YR]-1
			END,
		datepart(month, [PDate]),
		datepart(day, [PDate])),
	-- SQL 2008:
	DateAdd(day, datepart(day, [PDate]) - 1, 
          DateAdd(month, datepart(month, [PDate]) - 1, 
              DateAdd(Year, CASE WHEN DATEPART(month, [PDate]) < 10
			THEN [YR]
			ELSE [YR]-1
			END-1900, 0)))
FROM
(
	SELECT	[PDate] = CONVERT(date, '20141003'),
		[YR] = 2015
	UNION ALL SELECT CONVERT(date, '20141019'), 2016
) AS X

#6

DATEADD(YEAR, -YEAR(pdate) + [YR] - case when MONTH(pdate) >= 10 then 1 else 0 end, pdate)

For example:

select *, DATEADD(YEAR, -YEAR(pdate) + [YR] - case when MONTH(pdate) >= 10 then 1 else 0 end, pdate)
from (
    select '2015' as yr, CAST('20141003' as date) as pdate union all
    select '2015', '20140930'
) as test_data

#7

DATEADD(YEAR, -YEAR(pdate) + [YR] - case when MONTH(pdate) >= 10 then 1 else 0 end, pdate)

This worked great and was very simple. Thank you!!
Wish I could figure out how to Like a reply.


#8

Press the "Heart" icon under the relevant post :smile: