asked on

SQL Update Statement with 'If' condition

I've created an update statement in MS Access as follows:

UPDATE myTable SET myDateColumn = IIf(1=1, Null, Now());

which works, setting myDateColumn to Null. If I change the expression in the 'IIf' statement to 1=2 then the column updates to todays date.

I need to re-write this statement for use in a vb.net application.

Can someone provide the correct syntax?

Kyle Abrahams, PMP

UPDATE myTable SET myDateColumn = case when 1=1 then Null else getdate() end

ste5an

See IIF() (SQL Server 2012+):

UPDATE  myTable
SET     myDateColumn = IIF(1 = 1, NULL, GETDATE());

Open in new window

kaufmed

What do you mean by, "...for use in a vb.net application." Are you looking for the VB.NET equivalent of an IIF, or are you saying you want to invoke that query from VB.NET...or something else?

Tim313

ASKER

I'm looking for the VB.NET equivalent of an IIF.

Kyle Abrahams, PMP

as long as you're using 2008 or above you should be able to call just if:
https://msdn.microsoft.com/en-us/library/bb513985.aspx

if (1=1, Nothing, DateTime.Now)

however if you're executing it against a database you would use the SQL version I posted with the case statement.

ASKER CERTIFIED SOLUTION

Tim313

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

Nope. Now.ToShortDateString depends on the locals, while an fixed format (en-US) works always.

Tim313

ASKER

Found the solution myself.

ste5an

Caveat: Your solution depends on the locales used by the application. Use an explicit format.