CODEWITHSUNDEEP
sqlsql-server-2008datetimetype-conversionvarchar
Saranya Rajendran
Saranya Rajendran

Reputation: 757

Varchar to datetime conversion in SQL Server 2008

I need to convert varchar to datetime. I've tried something like following

 declare @datevarchar(150)='01/10/15' 
 declare @datenew date

 select 
     @datenew = convert(smalldatetime, CONVERT(nvarchar(10), CAST(@date AS DATETIME), 101)  ,101)

print @datenew

The output is : 2015-01-10

But when I change the date to

 declare @date  varchar(150)='13/10/15'

It throws the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The resultant format of datetime is YYYY-MM-DD. I want the result in the format DD-MM-YYYY..

What do I do?

Thanks in advance

Upvotes: 0

Views: 843

Answers (2)

Mathew Jacob
Mathew Jacob

Reputation: 54

   SET DATEFORMAT dmy; --ADD this command
   DECLARE @date as date  = '13/10/15'; --CHANGE the type to date
   declare @datenew date

 select 
        @datenew = convert(smalldatetime, CONVERT(nvarchar(10), CAST(@date AS DATETIME), 101)  ,101)

   print @datenew

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

The convert style that fits the dd/MM/yyyy string representation is 103, while the 101 style fits MM/dd/yyyy.
This, along with the fact that 01 is a valid day and also a valid month, while 13 is only a valid day, is the reason your first conversion successed while your last one failed.

So, if you have a string that represents a date with the dd/MM/yyyy format, you can convert it to a date like this:

DECLARE @Datestring char(10) = '13/09/2016' -- See note #1

SELECT CONVERT(Date, @DateString, 103)

The result would be a date value representing September's 13th, 2016.

Notes:
#1 - There is no need to use nvarchar(150) to represent a fixed-length string that all it's chars are ascii-compatible. That's why my string is char(10).

#2 - As I wrote in the comments - The result is a date datatype and as such it have no display format. If you are looking for a display format then you need a string representing a date value.

Upvotes: 1

Related Questions