Varchar to datetime conversion in SQL Server 2008

Question

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

Just define the variable as `2015-01-10`. Use a standard date format and your problems will be solved. — Gordon Linoff, Feb 02 '16 at 12:32
If you look up code 101 here https://msdn.microsoft.com/en-AU/library/ms187928.aspx you'll see it is expecting m/d/y format (and 13 is not a month). Can you take a step back and explain what you are really trying to do? Why do you care what the format of a date is? that is decided by whatever client tool you happen to be using at the time. — Nick.Mc, Feb 02 '16 at 12:38
[The result is a date and therefor not stored with display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) — Zohar Peled, Feb 02 '16 at 12:38
Always stick to the standard YYYY-MM-DD format. (If you for some reason want to display in other format, do the conversion as late as possible.) — jarlh, Feb 02 '16 at 12:57

score 1 · Accepted Answer · edited May 23 '17 at 11:45

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.

score 1 · Answer 2 · answered Feb 02 '16 at 13:28

   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

Varchar to datetime conversion in SQL Server 2008

2 Answers2