How to convert this varchar values to a datetime

Question

Hello people,

I want to convert a varchar value that iI set up to a date time value. I want to load rows of a database into another database, but conversion is not going well.

This is my query:

select Krant
       , cast(jaar as varchar(4))+'-'
          +RIGHT('0'+cast(maand as varchar(2)),2)+'-'
          +RIGHT('0'+cast(dag as varchar(2)),2) as datum
       , Inhoud as artikel
       , LEN(Inhoud)-LEN(Replace(Inhoud,' ','')) as numwords
       , 'Goirle' as vestiging 
from [Sitecore_Bibliotheekmb_Krantenknipsel].[dbo].[KRANGOI]

The cast to Datum has to be a datetime value, but i am not getting it to work properly. When i tried to cast to datetime it gave me an out of range exception.

These are the results of this query:

I want the "Datum" field to be a Datetime field with the same values but in Datetime format. Could anyone help me please :).

Thanks,

Younes

Answer 1

You are not casting the Varchars to a Datetime

CAST and CONVERT on MSDN.

Can you try this:

SELECT CAST(('2010' + '-' + '01' + '-' + '06') AS DATETIME)

Answer 2

Use this:

select Krant, cast(cast(jaar as varchar(4))+'-'
  +RIGHT('0'+cast(maand as varchar(2)),2)+'-'
  +RIGHT('0'+cast(dag as varchar(2)),2) as datetime) as datum, 
  Inhoud as artikel, 
  LEN(Inhoud)-LEN(Replace(Inhoud,' ','')) as numwords, 
  'Goirle' as vestiging 
from [Sitecore_Bibliotheekmb_Krantenknipsel].[dbo].[KRANGOI]

Answer 3

If any of your dates are earlier than 1753, a simple datetime type will not be sufficient. For this, you'd need to use the datetime2 datatype introduced in SQL Server 2008.

Also check that all values of maand and dag are valid days and months.