CODEWITHSUNDEEP
t-sqlif-statement
nick gowdy
nick gowdy

Reputation: 6531

T-sql IF Condition date evaluation

I have a simple question regarding T-SQL. I have a stored procedure which calls a Function which returns a date. I want to use an IF condition to compare todays date with the Functions returned date. IF true to return data.

Any ideas on the best way to handle this. I am learning t-sql at the moment and I am more familar with logical conditions from using C#. 

ALTER FUNCTION [dbo].[monday_new_period](@p_date as datetime) -- Parameter to find current date
RETURNS datetime
BEGIN 

-- 1 find the year and period given the current date

-- create parameters to store period and year of given date
declare @p_date_period int, @p_date_period_year int

-- assign the values to the period and year parameters
select 
@p_date_period=period, 
@p_date_period_year = [year] 
from client_week_uk where @p_date between start_dt and end_dt

-- 2 determine the first monday given the period and year, by adding days to the first day of the period
-- this only works on the assumption a period lasts a least one week

-- create parameter to store the first day of the period
declare @p_start_date_for_period_x datetime
select @p_start_date_for_period_x = min(start_dt)
from client_week_uk where period = @p_date_period and [year] = @p_date_period_year

-- create parameter to store result
declare @p_result datetime

-- add x days to the first day to get a monday
select @p_result = dateadd(d, 
    case datename(dw, @p_start_date_for_period_x) 
        when 'Monday' then 0
        when 'Tuesday' then 6
        when 'Wednesday' then 5
        when 'Thursday' then 4
        when 'Friday' then 3
        when 'Saturday' then 2
        when 'Sunday' then 1 end,
    @p_start_date_for_period_x)

Return @p_result
END

ALTER PROCEDURE [dbo].[usp_data_to_retrieve]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF monday_new_period(dbo.trimdate(getutcdate()) = getutcdate()

BEGIN

-- SQL GOES HERE --

END

Thanks!!

Upvotes: 0

Views: 7466

Answers (2)

breathingdust
breathingdust

Reputation: 205

This is the basic syntax for a T-SQL IF and a date compare.

If you are comparing just the date portion for equality you will need to use:

select dateadd(dd,0, datediff(dd,0, getDate()))

This snippet will effectively set the time portion to 00:00:00 so you can compare just dates. So in use it will look something like this.

IF dateadd(dd,0, datediff(dd,0, fn_yourFunction())) = dateadd(dd,0, datediff(dd,0, GETDATE()))
BEGIN
   RETURN SELECT * FROM SOMEDATA
END

Hope that helps!

Upvotes: 0

Tomek
Tomek

Reputation: 3279

I assume you are working on Sql2008. See documentation of IF and CASE keywords for more details.

CREATE FUNCTION dbo.GetSomeDate()
RETURNS datetime
AS
BEGIN
    RETURN '2012-03-05 13:12:14'
END

GO


IF CAST(GETDATE() AS DATE) = CAST(dbo.GetSomeDate() AS DATE)
BEGIN
    PRINT 'The same date'
END
ELSE
BEGIN
    PRINT 'Different dates'
END

-- in the select query
SELECT CASE WHEN CAST(GETDATE() AS DATE) = CAST(dbo.GetSomeDate() AS DATE) THEN 1 ELSE 0 END AS IsTheSame

Upvotes: 2

Related Questions