CODEWITHSUNDEEP
sqldatabasedatetimesql-server-2012
Vinay
Vinay

Reputation: 1064

Set Date from another table in SQL Server

I have a code in VB.Net application which I would like to move to stored procedure. VB code looks something like this : 

if(id == 3)
{
  var year = Year(invoiceDate)
  updatedDate = DateSerial(dueYear, DueDateMonth, DueDateDay)

  If updatedDate < invoiceDate Then
     updatedDate += 1
     updatedDate = DateSerial(updatedDate , getMonthFromDBTable, getDayFromDBTable)
  End If
}

This is part of a condition which I am trying to resolve.

Currently in SQL I have the following 

DECLARE @tmpCalendarDate DATETIME;
DECLARE @tmpYear int;

SET @tmpCalendarDate = convert(varchar(10), getdate(),120);
SET @tmpYear = DATEPART(yyyy, @tmpCalendarDate);

SELECT COALESCE (
       CASE WHEN tt.ID = 1 THEN DATEADD(day, t.DaysUntilDue, r.InvoiceDate) END,
       CASE WHEN tt.ID = 3 THEN -- This is where I need to add the condition.

I was thinking of setting the @tmpCalendarDate with the values to look something like 

CASE WHEN tt.ID = 3 THEN @tmpCalendarDate = '@tmpYear-t.DueDateMonth-t.DueDateDay'

where t is a table.

This value cannot be changed, so I would rather calculate and fetch it once rather than calculating it every time binding changes (wpf application).

Any help is greatly appreciated.

UPDATE: I realized maybe I am vague with my question, so here it is How do i set @tmpCalendarDate? I tried

SELECT @tmpCalendarDate = '@tmpYear-t.DueDateMonth-t.DueDateDay' FROM table t

and I get an error 'Conversion failed when converting date and/or time from character string.' Instead I am expecting something like @tmpCalendarDate to be set to '2016-03-12'

Also, can I add an If..Else condition inside CASE.Then In my example: 

CASE WHEN tt.ID = 3 THEN @tmpCalendarDate = '@tmpYear-t.DueDateMonth-t.DueDateDay'
    IF (@tmpCalendarDate > InvoiceDate)
    BEGIN
      --Do some logic
    END
    ELSE
      --Do some logic
    BEGIN
    END

Upvotes: 0

Views: 49

Answers (1)

SQLChao
SQLChao

Reputation: 7847

You can use DATEFROMPARTS

@tmpCalendarDate = DATEFROMPARTS(@tmpyear, t.DueDateMonth, t.DueDateDay)

Your mistake in your original attempt is you are setting @tempCalendarDate to actual string @tmpYear-t.DueDateMonth-t.DueDateDay which results in a conversion error.

Upvotes: 1

Related Questions