CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Asad
Asad

Reputation: 21

SQL Server 2008 Date parameter

I'm passing a start date and end date parameter to my stored procedure. I'm doing a simple test here:

DECLARE @StartDate DATE = '10/06/2013' --dd/mm/yyyy
SELECT @StartDate   -- this statement running successfully 

DECLARE @EndDate DATE = '30/06/2013'  --dd/mm/yyyy
SELECT @EndDate -- this statement giving error

This statement returns the following error

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Does anybody have idea what's going wrong with EndDate?

Upvotes: 2

Views: 136

Answers (3)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

By default sql server takes date value in 'yyyy-mm-dd' format,

So you need to follow that format or you need to convert date format accordingly.

T-SQL convert string to datetime - SQL Server convert string to date 

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- SQL string to datetime conversion without century - some exceptions
SELECT convert(datetime, '10/23/16', 1)   -- mm/dd/yy
SELECT convert(datetime, '16.10.23', 2)   -- yy.mm.dd
SELECT convert(datetime, '23/10/16', 3)   -- dd/mm/yy
SELECT convert(datetime, '23.10.16', 4)   -- dd.mm.yy
SELECT convert(datetime, '23-10-16', 5)   -- dd-mm-yy
SELECT convert(datetime, '23 OCT 16', 6)  -- dd mon yy
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy

Upvotes: 0

Esty
Esty

Reputation: 1912

'10/06/2013' means 06-Oct-2013 not 10-Jun-2013. There is nothing exists with month 30 as in your @EndDate '30/06/2013'.

DECLARE @StartDate DATE='10/06/2013' 
DECLARE @DummyDate DATE = '2013-Oct-06'

IF @StartDate = @DummyDate
BEGIN
    SELECT 1
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270643

I'm pretty sure the error is on this line:

DECLARE @EndDate DATE = '30/06/2013'  --dd/mm/yyyy

Not on the SELECT. It wouldn't make sense that it would be on the SELECT, because processing a variable should be fine.

I would recommend that you use YYYYMMDD formats. The following is my preference:

DECLARE @EndDate DATE = '2013-30-06' ;

However, it can fail for certain internationalization settings. The following is documented to always work:

DECLARE @EndDate DATE = '20133006' ;

Upvotes: 5

Related Questions