how convert string variable to datetime variable in sql-server?

Question

I'm relatively new to sql-server; I'm trying to have a start-date and end-date pulled from a form-variable as string then convert it into datetime (yyyy-mm-dd) format and I can't seem to find anything that works. Attempted code and resulting error is below. Any advice would be appreciated.

declare @startdate as varchar
declare @enddate as varchar
set @startdate=cast(@startdate as datetime)
set @enddate=cast(@enddate as datetime)

SELECT order_date, inv_no 
from invoices 
where order_date between @startdate and @enddate

The error I keep getting is:

Conversion failed when converting datetime from character string.

How do I fix this?

You need to specify a length for your variables to begin with to stop them being truncated to 1 character. Also yyyy-mm-dd doesn't work on all locales for datetime. yyyymmdd is more robust. — Martin Smith
– Martin Smith, Commented Oct 12, 2012 at 15:09

Taryn · Accepted Answer · 2012-10-12 15:10:41Z

2

specify a length for your varchar:

declare @startdate as varchar(10)
declare @enddate as varchar(10)
set @startdate=cast(@startdate as datetime)
set @enddate=cast(@enddate as datetime)

SELECT order_date, inv_no 
from invoices 
where order_date between @startdate and @enddate

answered Oct 12, 2012 at 15:10

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

joby-flick · Accepted Answer · 2016-03-31 20:27:38Z

0

you don't have to cast necessarily

declare @startdate varchar(50)
declare @enddate varchar(50)
declare @start datetime = @startdate, @end datetime = @enddate
select @start, @end

edited Mar 31, 2016 at 20:27

answered Mar 15, 2016 at 15:46

joby-flick

1,8381 gold badge14 silver badges22 bronze badges

Collectives™ on Stack Overflow

how convert string variable to datetime variable in sql-server?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related