Return Date Time instead of NULL in field

I would like to return the current datetime in C3 if it equals NULL I then want to convert it into the same format that the system is using, the output looks something like this 1187901969 I have the code to convert it from a different table it looks like this
CONVERT(datetime, Date_Opened / 86402.027 + 25567) AS [Date Opened] I need it to do the oposite
                      TOP 100 PERCENT CASE WHEN t .C1 IS NULL 
                      THEN ul.EmployeeID COLLATE Latin1_General_CI_AI ELSE t .C1 COLLATE Latin1_General_CI_AI END AS C1, CASE WHEN t .C2 IS NULL 
                      THEN 'LDAP' ELSE t .C2 COLLATE Latin1_General_CI_AI END AS C2, t.C3, t.C4, CASE WHEN t .C5 IS NULL 
                      THEN 'LDAP' ELSE t .C5 COLLATE Latin1_General_CI_AI END AS C5, t.C6, t.C7, AS C8, ul.givenName AS C536870912, 
                      ul.telephoneNumber AS C536870914, ul.personalTitle AS C536870915, t.C536870917, t.C536870919, t.C536870920, t.C536870921, 
                      ul.mail AS C536870922, t.C536870923, ul.initials AS C536871076, t.C536871082
FROM         [LDAP Connection].dbo.User_List AS ul LEFT OUTER JOIN
                      dbo.t37 AS t ON = t.C8 AND ul.givenName = t.C536870912
WHERE     (ul.employeeID IS NOT NULL)

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
Try something like this instead of t.C3:
coalesce(t.C3,CONVERT(integer, GETDATE())-25567*86402.027)

Open in new window

Victoria YudinOwner / Dynamics GP ConsultantCommented:
Sorry, small change, I think this makes the math more correct:
SELECT coalesce(t.C3,(CONVERT(integer, GETDATE())-25567)*86402.027)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
141ANGAuthor Commented:
how do I get this to run only if the value is NULL?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
coalesce will give you the value in C3 as it is if it is not NULL
if the value is NULL, only then will it run the code after the first comma, so:

coalesce(value if it is not null, what to do if value is null)
141ANGAuthor Commented:
Never mind I got it to work here is what I did
                      - 25567) * 86402.027) ELSE t .C3 END AS C3

Open in new window

141ANGAuthor Commented:
Awesome thankyou so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.