CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Glory Raj
Glory Raj

Reputation: 17701

How to compare two given dates with date coming from datetime field in sql server

I have got user entered Datetime fields like CreatedON and CreatedEND and these are having datetime formats like (29-02-2013) and (12-04-2013) (these are sample values only)

I have got one column in sql server like CreatedDatetime and values like this 2013-09-03 and 2013-02-03 (these are sample values only) Now I need to check whether the CreatedDatetime field is in between CreatedON and CreatedEND two given dates...

How can i compare the date coming from database with these two values.. this comparison need to be done in sql server only

Would any one suggest any ideas and solutions for this .. Many thanks In advance..

EDIT :

   DECLARE  @return_value int

EXEC    @return_value = [dbo].[tp_SelectTransactionHistorySearch]
        @OffSetRowNo = 1,
        @FetchRowNo = 1,
        @StatusSelection = N's',
        @isReviewed = NULL,
        @ProjectCaseNumber = NULL,
        @CostPageNumber = NULL,
        @TransactionTypeChange = NULL,
        @DescriptionChange = NULL,
        @TrasactionCreateOnBeginDate = N'19-03-2013',
        @TransactionCreatedOnEndDate = N'20-03-2013',
        @TransactionUpdatedOnBeginDate = N'10-04-2013',
        @TransactionUpdateOnEndDate = N'11-04-2013',
        @ItemID = NULL

SELECT  'Return Value' = @return_value

GO

ERROR : Msg 8114, Level 16, State 5, Procedure tp_SelectTransactionHistorySearch, Line 0 Error converting data type nvarchar to datetime.

Upvotes: 1

Views: 1346

Answers (2)

Fabio
Fabio

Reputation: 32445

If user entering a date from another system/program then use parameters:

DECLARE @CreatedON AS DATETIME = '';
DECLARE @CreatedEND AS DATETIME = '';

SELECT 
    CASE WHEN CreatedDatetime BETWEEN @CreatedON AND @CreatedEnd 
    THEN 'True' ELSE 'False' END

But if values are already stored in database then just compare fields as

SELECT 
    CASE WHEN CreatedDatetime BETWEEN CreatedON AND CreatedEnd 
    THEN 'True' ELSE 'False' END
FROM TimeTable

Or if they stored as string(dd-mm-yyyy) then:

SELECT 
    CASE WHEN CreatedDatetime BETWEEN CONVERT(DATETIME, CreatedON, 105) AND
              CONVERT(DATETIME, CreatedEnd, 105) THEN 'True' ELSE 'False' END

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try using CAST like this:-

(CreatedDatetime >= CAST(CreatedON AS DATE)) AND (CreatedDatetime < CAST(CreatedEND AS DATE))

Upvotes: 2

Related Questions