Conversion failed when converting date and/or time from character string in sql server

Question

I have a function to convert a string to datetime (101) format. But it gives me an error when I convert this value.

2016-03-01 00:00:00.0000000

And the error is

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

And my function is ..

ALTER FUNCTION [dbo].[ConvertToDate] 
(
    @Value nVarchar(MAX)
)
RETURNS DATETIME
AS
BEGIN
    IF (@Value <> NULL OR @Value <> '')
        BEGIN
            DECLARE @dt DATETIME
            SET @dt = CONVERT(DATETIME,@Value,101)
            --SET @dt = CAST(@Value AS DATETIME(101))
            RETURN @dt
        END

        RETURN NULL

END

What is the problem?

Side note: @Value <> NULL. If you compare anything with NULL using <> the result is NULL, and not true or false. The correct way is @Value IS NOT NULL — ughai
– ughai, Commented Jun 15, 2015 at 5:22

Mukesh Kalgude · Accepted Answer · 2015-06-15 05:19:31Z

3

It is problem your data. It is not converting. Your data should be like this

'2016-03-01 00:00:00.0000000'

But you can change your data like this

'2016-03-01 00:00:00.000'

answered Jun 15, 2015 at 5:19

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tarzanbappa Over a year ago

how can I change that.. is there any way to convert them

Arunprasanth K V Over a year ago

@tarzanbappa if you want to convert then you can use substring like below DECLARE (at)dt VARCHAR(50) ='2016-03-01 00:00:00.0000000' select CAST(SUBSTRING((at)dt,1,16) AS datetime) , but is is not necessary you can directly use datetime2 that is better option

Raj · Accepted Answer · 2015-06-15 05:25:31Z

2

Too many zeros in the millisecond part. This works fine

DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000'
SELECT CONVERT(DATETIME,@Value,101)

You can try using LEFT like this

DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000000'
SELECT CONVERT(DATETIME,LEFT(@Value,23),101)

answered Jun 15, 2015 at 5:25

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Comments

mohan111 · Accepted Answer · 2015-06-15 05:23:53Z

1

DECLARE @dt VARCHAR(50) ='2016-03-01 00:00:00.000000'

select CAST (@dt AS DATETIME2)

answered Jun 15, 2015 at 5:23

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Conversion failed when converting date and/or time from character string in sql server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related