Case Statement to produce one time date/time stamp based on value in another field

1

Good morning,
I am building a case statement within an application called Motivity (BI platform for the mortgage industry). The statement is attempting to place a one time date and time stamp within a field depending on a value being input into another field. The code I have is failing currently. Can someone point out any issues with what I have? Code is below; DocPrepContactName is the dependent field. Any value in that field should kick off the date/time stamp. DocPrepDateTime is the field into which I'm trying to place the date/time stamp.

<case
when DocPrepContactName IS NOT NULL And DocPrepDateTime IS NULL then FORMAT (getdate(), 'mm/dd/yyyy, hh:mm:ss')
else DocPrepDateTime
end>

2

Welcome,

to better answer your question it would help if you provided some sample data as follows

declare @sample table(DocPrepContactName  varchar(150), DocPrepDateTime datetime)

insert into @sample 
select 'Donuts Bavarian Kreme', getdate()

What you have looks good to me but we would need to see real data that is causing you issues. "The code I have is failing currently." is not going to help us help you

1 Like
3

Assuming you're getting a data type conversion error, because your CASE expression could return a datetime or a varchar value, which is not permitted. A minor change will fix that:

FORMAT(CASE
WHEN DocPrepContactName IS NOT NULL AND DocPrepDateTime IS NULL 
THEN getdate()
ELSE DocPrepDateTime END, 'mm/dd/yyyy, hh:mm:ss')

If that's not the error you're getting, or that doesn't fix it, then we'll need to see sample data and expected results.

2 Likes
4

What is the data type of the column DocPrepDateTime? If it is a varchar - the it really should be defined as a datetime data type. If it is a datetime data type then you don't need to format.

Is it possible for DocPrepContactName to be a blank/empty string?

You could also do something like this:

Case When nullif(DocPrepContactName, '') Is Not Null THEN coalesce(DocPrepDateTime, getdate()) Else DocPrepDateTime End

Or - you can reverse it, which is simpler:

Case When nullif(DocPrepContactName, '') Is Null Then Null Else coalesce(DocPrepDateTime, getdate()) End

If it is possible for a someone to add a prep contact name - and then remove that value (or blank it out), the above then removes the DocPrepDateTime. If that is not the desired result - then change it to:

Case When nullif(DocPrepContactName, '') Is Null Then DocPrepDateTime Else coalesce(DocPrepDateTime, getdate()) End

As to the error you are getting - if the DocPrepDateTime is actually a datetime data type (as I suspect) then the format you are using is invalid (and isn't needed anyways). In the format statement there are 2 issues - the first being the comma which isn't recognized by SQL Server as a valid date format and fails conversion - and the second problem is for month you need MM and not mm (minutes).

And finally - if that column is a string (varchar) it needs to be changed to a datetime data type. If that isn't possible then you really need to change the format to a non-ambiguous format. MM/DD/YYYY HH:MM:SS will be interpreted as DD/MM/YYYY HH:MM:SS on a system with the language set to British English (for example). A valid format for SQL Server that will always be interpreted correctly is YYYYMMDD HH:MM:SS - and can be returned using CONVERT instead of FORMAT (which is much slower than using CONVERT).

1 Like
5

Thanks for the replies everyone; the field DocPrepDateTime is a small string data type currently, which is needed for the format syntax. However, if I strip off the format syntax and just go with getdate() I believe I can change it to a date/time data type. I'm unsure of the pros/cons of either approach. I'm attaching the output screenshot.2021-05-07_11-15-33

6

Sorry, I've also confirmed that the docprepcontactname is not an empty string. Data is in that field attribute.

7

please provide your sample data as follows and not as an image. help us help you.

declare @sample table(DocPrepContactName  varchar(150), DocPrepDateTime datetime)

insert into @sample 
select 'Donuts Bavarian Kreme', getdate()
8

I'm not familiar with the sample data you're refencing. This is a field integration case statement from our Loan origination system (LOS) directly into our BI tool (motivity). VEND.X317 is the LOS field that is interpreted into Motivity as DocPrepContactName. Using that interpretation field, I'm attempting to create an additional field for the date/time stamp. I'm unsure of which sample data set would be helpful here.

9

The advantage of using a datetime data type is that you have the correct data type for the expected data and can perform date math without any conversions. With an invalid string type - which is how your format is currently set, you cannot do any of that without manipulating the string.

If you can change it to a datetime - then change it, you will be much happier in the long term.

Note: what happens in the application if a user enters 3 spaces into the field DocPrepContactName? Is that saved as a string with 3 spaces - or does the application convert that to a null value - or something else?

1 Like
10

Thanks I have it changed now. I'll have to test the 3 spaces or other random entries into that docprepcontactname field. However, right now I can't get the code to show right for when they enter their name as expected. I'm suspecting the issue is with limitations on the backend of the BI application and not so much on the code. The logic seems straight forward.

11

Yes, you have access to Loan origination system (LOS) and the BI tool (motivity) but we don't have access to those resources. So providing sample data makes it so that we can emulate the data in your evironment in our sql server and attempt to provide you with an answer. doesn't seem like you have yet because no one knows the data type and data you are working with