-1

i am pretty new to SQL.
I am trying to show in a select since when the customer is in the database but i am getting the error Conversion failed when converting date and/or time from character string.

Here is the code which i try to show the date.

SELECT Naam + ' is klant sinds ' + [Klant sinds] FROM tblKlant

[Klant sinds] is a datetime type.

Thanks for your time!

Jens
  • 67,715
  • 15
  • 98
  • 113
Jari
  • 31
  • 1
  • 7
  • Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) – Stefano Zanini May 17 '17 at 09:07
  • cast it to varchar like: `SELECT Naam + ' is klant sinds ' + CAST([Klant sinds] as nvarchar(50)) FROM tblKlant` but you may not get it into the desired format – BytesOfMetal May 17 '17 at 09:13
  • Why are you formatting the text of your select list in a sql query. Thats what UI code is for. Select your 2 fields and format in your favourite UI coding platform. – Jamiec May 17 '17 at 09:15

3 Answers3

0

SELECT Naam + ' is klant sinds ' + CONVERT(VARCHAR,[Klant sinds],101) FROM tblKlant

101=US date/time format
For a complete list of possible date/time formats

SBF
  • 1,252
  • 3
  • 12
  • 21
0

Try this : SELECT CAST(Naam as varchar) + ' is klant sinds ' + CAST([Klant sinds] as varchar) FROM tblKlant

I have casted Naam as varchar to ensure everything is in varchar/string type

Prabhat G
  • 2,974
  • 1
  • 22
  • 31
0

Since SQL Server 2012 you can use CONCAT to concatinate all sorts of values together, without having to worry about casting or NULL values:

DECLARE @v_naam           NVARCHAR(100); SET @v_naam = N'Mic Mac Jampudding';
DECLARE @v_date           DATETIME;      SET @v_date = GETDATE();
DECLARE @v_somenullvalue  NVARCHAR(10);  SET @v_somenullvalue = NULL;
DECLARE @v_aninteger      SMALLINT;      SET @v_aninteger = 5;

SELECT CONCAT(@v_naam, ' is klant sinds ', @v_date, '. Some null value: ', @v_somenullvalue, ' and a lovely int: ', @v_aninteger);

Output:

Mic Mac Jampudding is klant sinds May 17 2017 11:27AM. Some null value:  and a lovely int: 5

If you just want the date without having the time part on your DATETIME you can use the following:

CAST(@v_date AS DATE)
Jens
  • 3,249
  • 2
  • 25
  • 42