Cast using a variable ver.2

Dear Experts,
I'm trying to convert a varchar into a date or datetime (I tried both of them) using a variable but all the time I've got back an error
"Conversion failed when converting date and/or time from character string"

Open in new window


DECLARE @dateFormInvoice as nvarchar(10) = '16.04.2015'

PRINT CONVERT(datetime2, @dateFormInvoice);
PRINT CAST(@dateFormInvoice as datetime2);

Open in new window


Any idea how to solve that

Thank you
gokyo66Asked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
The following gives:
2015-04-16
DECLARE @dateFormInvoice as nvarchar(10) = '16.04.2015'
Set @dateFormInvoice=substring(@dateFormInvoice,7,4) + 
                     substring(@dateFormInvoice,4,2) +
		     substring(@dateFormInvoice,1,2);

PRINT CONVERT(date, @dateFormInvoice);

Open in new window


But, it could be displayed in any other format you want.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

DECLARE @dateFormInvoice as nvarchar(10) = '20150416'

Store it as YYYYMMDD in the database but display it any way you want in your screen.

This is language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.
0
 
gokyo66Author Commented:
Point is that the @dateFromInvoice is a date that is read from an application and the format is that dd.mm.yyyy
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gokyo66Author Commented:
Actually I don't have to store it. Let me explain better with the following code. Pay attention that the syntax is weird because is made in SAP

/*@dateFromInvoice store the date displayed in the Invoice document if you want to print the value of @dateFromInvoice you got back for instance today date 17.04.2015 (Switzerland format)*/
DECLARE @dateFromInvoice as nvarchar(10)= $[OINV.DocDate]

Base on terms of payment of one particular customer I've  to recalculate the due date and put the value and the field of the form Doc due date. Since for doing that recalculation I use the function datedd I need to convert @dateFromInvoice in a date format so I can use in the dateadd function
0
 
PortletPaulfreelancerCommented:
use style number 104

 convert(varchar, your_data_here ,104)

see http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html


DECLARE @dateFormInvoice as nvarchar(10) = '16.04.2015'
SELECT CONVERT(datetime2, @dateFormInvoice, 104);
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Using Paul's solution, if you don't want time portion, just use date instead of datatime2:

SELECT CONVERT(date, @dateFormInvoice, 104);
0
 
gokyo66Author Commented:
Thank you appreciate your support
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.