Converting string to datetime in yyyymmdd format

Question

I need to add days to a varchar representation in the yyyymmdd format.

I'm getting an error when converting a varchar(8) value to datetime in SQL Server 2005.

I don't have a clue why!

Here is what works:

select
   convert (datetime, '20111019')
from _table

This does not work, although the value in the column is 20111019

select 
   convert (datetime, RechDatum)
from _table

I already tried:

convert (datetime, RechDatum, 112)

and

SET DATEFORMAT ymd

I was able to solve the problem with an own function. But I'm still confused and would be glad for answers of you!

IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'getDateFromString' and type = 'FN')
DROP FUNCTION getDateFromString
GO
CREATE FUNCTION getDateFromString (@Cyear VARCHAR(4), @Cmonth smallint, @Cday smallint, @offsetdays smallint)
RETURNS DATETIME
AS BEGIN
    DECLARE @day SMALLINT 
    DECLARE @month SMALLINT
    DECLARE @year SMALLINT
    SET @day = CAST (@Cday AS SMALLINT)
    SET @month = CAST (@Cmonth AS SMALLINT)
    SET @year = CAST (@Cyear AS SMALLINT)
    RETURN DateAdd(day, @offsetdays,
                (SELECT 
                    DateAdd (day, @day -1,
                        DateAdd (month, @month -1,
                            DateAdd (Year, @year-1900,0)))  ))
END

GO

Here is the table definition, it's part of an german ERP system:

CREATE TABLE [dbo].[AuftrKopf](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IsSammelRechnung] [bit] NULL,
    [AuftragsNr] [int] NULL,
    [Kd_ID] [int] NULL,
    [Fahrer_ID] [int] NULL,
    [Debitor] [int] NULL,
    [Fz_ID] [int] NULL,
    [FHFz_ID] [int] NULL,
    [AuftrArt_ID] [int] NULL,
    [AuftrHerkunft_ID] [int] NULL,
    [AuftrZustand_ID] [int] NULL,
    [RechnungsArt_ID] [int] NULL,
    [AuftragVom] [varchar](8) NULL,
    [FertigAmUm] [varchar](12) NULL,
    [BringenAmUm] [varchar](12) NULL,
    [AbgeschlossenAmUm] [varchar](12) NULL,
    [StempelkartenNr] [int] NULL,
    [HuetchenNr] [int] NULL,
    [LeistungsDatum] [varchar](8) NULL,
[RechnDatum] [varchar](8) NULL,
    [ValutaDatum] [varchar](8) NULL,
    [NettoSummenspalte1] [decimal](18, 2) NULL,
    [NettoSummenspalte2] [decimal](18, 2) NULL,
    [NettoSummenspalte3] [decimal](18, 2) NULL,
    [NettoSummenspalte4] [decimal](18, 2) NULL,
    [NettoSummenspalte5] [decimal](18, 2) NULL,
    [NettoSummenspalte6] [decimal](18, 2) NULL,
    [NettoSummenspalte7] [decimal](18, 2) NULL,
    [NettoSummenspalte8] [decimal](18, 2) NULL,
    [NettoSummenspalte9] [decimal](18, 2) NULL,
    [NettoSummenspalte10] [decimal](18, 2) NULL,
    [NettoOhneSummenSpalte] [decimal](18, 2) NULL,
    [MwStSummenspalte1] [decimal](18, 2) NULL,
    [MwStSummenspalte2] [decimal](18, 2) NULL,
    [MwStSummenspalte3] [decimal](18, 2) NULL,
    [MwStBasisspalte1] [decimal](18, 2) NULL,
    [MwStBasisspalte2] [decimal](18, 2) NULL,
    [MwStBasisspalte3] [decimal](18, 2) NULL,
    [BasisspalteOhneMwSt] [decimal](18, 2) NULL,
[KdNr] [int] NULL,
    [KdNachname] [nvarchar](50) NULL,
    [KdVorname] [nvarchar](50) NULL,
    [KdTelefon] [nvarchar](30) NULL,
    [KdMobilTelefon] [nvarchar](30) NULL,
    [KdEmail] [nvarchar](100) NULL,
[Kennz] [nvarchar](20) NULL,
    [BerKennz] [nvarchar](20) NULL,
    [KM] [int] NULL,
    [Laufleistung] [int] NULL,
    [RabattStufe] [int] NULL,
    [Adresse_ID] [int] NULL,
    [CPrLieferschein] [int] NULL,
    [BincMwSt] [bit] NULL,
[LandWKuerzel] [nvarchar](4) NULL,
    [Bemerkung] [nvarchar](50) NULL,
[Verkaeufer_ID] [int] NULL,
    [Sachbearbeiter_ID] [int] NULL,
[PersIDFaktura] [int] NULL,
    [Monteur_ID] [int] NULL,
    [Team_ID] [int] NULL,
    [CPrRepAngebot] [int] NULL,
    [CPrRepAuftr] [int] NULL,
    [CPrWerkstattkarte] [int] NULL,
    [CPrRechnung] [int] NULL,
    [CPrRechnungFormularName] [nvarchar](50) NULL,
    [CPrRepRechnKopie] [int] NULL,
    [CPrPickerzettel] [int] NULL,
    [CPrVorabRechn] [int] NULL,
    [CPrAuftrBestaet] [int] NULL,
    [BOhneFzDaten] [bit] NULL,
    [BOhneNr] [bit] NULL,
    [PrOhneEinzelpreis] [bit] NULL,
    [FHFzNW] [bit] NULL,
    [FHDifferenzbesteuert] [bit] NULL,
    [StornoZuAuftrNr] [int] NULL,
    [Storno_Datum] [varchar](8) NULL,
    [Buchungsdatum] [varchar](14) NULL,
    [VakBuchungsModus] [int] NULL,
    [DiffNettoKompZuSumme] [decimal](18, 2) NULL,
    [Vorgangsart_ID] [int] NULL,
    [Beschreibung] [ntext] NULL,
    [IsErreichbarTel1perSMS] [bit] NULL,
    [IsErreichbarTel1perTel] [bit] NULL,
    [IsErreichbarMobilperSMS] [bit] NULL,
    [IsErreichbarMobilperTel] [bit] NULL,
    [IsErreichbarEmail] [bit] NULL,
    [StundenGeschaetzt] [decimal](18, 2) NULL,
    [WKZ_Zweitwaehrung] [nvarchar](3) NULL,
    [Kurs_Zweitwaehrung] [decimal](18, 7) NULL,
    [EndbetragInZweitwaehrung] [bit] NULL,
    [Kulanzursprung] [int] NULL,
    [IsKulanzgesplittet] [bit] NULL,
    [SplitUrsprung] [int] NULL,
    [IsGesplittet] [bit] NULL,
    [FlottenReparaturkostenId] [int] NULL,
    [NovaSatz] [decimal](18, 2) NULL,
    [NovaGesamt] [decimal](18, 2) NULL,
    [NovaBasis] [decimal](18, 2) NULL,
    [NovaBonusMalus] [decimal](18, 2) NULL,
    [FilialNr] [int] NULL,
    [Angebotverfall] [varchar](8) NULL,
    [IvecoSonderverkauf] [bit] NULL,
    [LetzteAenderung] [varchar](14) NULL,
    [IsAnzahlung] [bit] NULL,
    [AnzahlungSumme] [decimal](18, 2) NULL,
    [SADESperre] [int] NULL,
    [SADEVersendet] [varchar](14) NULL,
    [MP2Angebotsnummer] [int] NULL,
    [SchadenNummer] [nvarchar](20) NULL,
    [ReferenzAudaNet] [nvarchar](40) NULL,
    [LetzterBearbeiter] [nvarchar](50) NULL,
    [Rechnername] [nvarchar](50) NULL,
    [ReferenzAudaNetCaseId] [nvarchar](40) NULL,
[AuftragsanlageOrder] [bit] NULL,
    [RabattDrucken] [bit] NULL,
    [AuftragStatus1_ID] [int] NULL,
    [AuftragStatus2_ID] [int] NULL,
    [AuftragStatus3_ID] [int] NULL,
    [AuftragStatus4_ID] [int] NULL,
    [TeileZurueckBeiGutschrift] [bit] NULL,
    [StornoGrund_ID] [int] NULL,
    [Anleger_ID] [int] NULL,
    [Betriebsstunden] [int] NULL,
    [SperreSADE] [bit] NULL,
    [FiOnlineContractId] [nvarchar](20) NULL,
    [DWNichtMehrFragen] [bit] NULL,
    [BestellZaehler] [int] NULL,
    [NovaOhneAufschlag] [bit] NULL,
    [DefaultLager_ID] [int] NULL,
    [RechnungBezahltKennzeichnen] [bit] NULL,
    [AnVWBackboneAsNewReported] [bit] NULL,
    [VWBackboneRequestID] [nvarchar](20) NULL,
    [VWBackboneConversationID] [nvarchar](70) NULL,
    [VWBackboneStatusID] [int] NULL,
    [VWBackboneSaga2ClaimNr] [nvarchar](2) NULL,
    [VWBackboneSaga2GarantieAntragNr] [int] NULL,
    [VWBackboneSaga2MaxClaims] [int] NULL,
 CONSTRAINT [PK_AuftrKopf] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The strange thing is, that the conversions work on my computer with german-swiss settings, but not on the client-computer, which has a french setup.

Can you script the table "as create", and post the column definition for RechDatum? — Andomar
– Andomar, Commented Jan 25, 2012 at 16:02

Mithrandir · Accepted Answer · 2012-01-25 17:46:30Z

4

This works:

DECLARE @datevar varchar(8) 
SET @datevar = '20111019'

SELECT CONVERT(DATETIME,@datevar,114) AS RechDatum

And this:

SELECT '20111019' AS RechDatum INTO #tmp
SELECT CONVERT(DATETIME,Rechdatum,114) AS RechDate from #tmp

works fine too. Are you sure you table column has the right type/format?

edited Jan 25, 2012 at 17:46

answered Jan 25, 2012 at 16:05

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

chris Over a year ago

I think that the column is ok. The problem with the CONVERT-function is, that the third parameter is ignored, when converting from string to datetime. It just works from datetime to string, although it doesn't throw a syntax-error. That is written in the microsoft documentation.

Mithrandir Over a year ago

Yes, i did? Can't find a column named "RechDatum"?!

chris Over a year ago

Sorry Mithrandir, I hope you didn't lose the interest over that. I posted the wrong table.

Mithrandir Over a year ago

So the problem is on system with a french locale, is it? On your swiss-german machine it's fine? I'm using a german SQL Server as well ...

Mithrandir Over a year ago

exec sp_helplanguage 'German' and exec sp_helplanguage 'French' say, that both have standard DATEFORMAT of dmy, this should not be a problem.

|

chris · Accepted Answer · 2012-01-26 12:54:14Z

2

I found the solution to the problem with the input of Laszlo Tenki. I read the SET DATEFORMAT-article of the Microsoft Help again: http://msdn.microsoft.com/en-us/library/ms189491.aspx

It seems that there are some character string formats, that are independent of SET DATEFORMAT. From the Microsoft-Article: Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting. For more information about how to use DATEFORMAT with different formats see the "String Literal Date and Time Formats" section in Using Date and Time Data.

The solution answer can be found here: http://msdn.microsoft.com/en-us/library/ms180878.aspx

I could solve the problem with

SET LANGUAGE

Set language sets the SQL-Server-Settings for the Current Session. The interpretation of String representations by the SQL-Server is dependent on SET DATEFORMAT and SET LANGUAGE. The format I chose ISO 8601 is 'SET DATETIME'-independent, thats why that didn't work. Check the info here: http://msdn.microsoft.com/en-us/library/ms180878.aspx

However: Microsoft recommends the usage of ISO 8601, because it is multilanguage-compatible. That raises the question, why I needed to change the LANGUAGE at all.

answered Jan 26, 2012 at 12:54

chris

5652 gold badges5 silver badges14 bronze badges

2 Comments

Las Ten Over a year ago

To be honest, I do not understand why this affects a date string purely made of digits. But I'm glad you could solve it!

Anthony Horne Over a year ago

Interesting to note that YYYYMMDD is independent of formatting settings. Very interesting find.

Las Ten · Accepted Answer · 2012-01-26 09:00:40Z

1

SQL server has its own date formatting mechanism that is independent from the regional settings of the computer.

I would say that it is probably different on your developer machine and the production server. It looks that you can process dates in either format, your only problem is that it changes and you don't know for sure what to prepare for.

For SET DATEFORMAT, See the details here: http://msdn.microsoft.com/en-us/library/ms189491.aspx

answered Jan 26, 2012 at 9:00

Las Ten

1,17510 silver badges22 bronze badges

1 Comment

chris Over a year ago

Thank you for your answer Laszlo. But I couldn't solve the problem with SET DATEFORMAT. I used SET DATEFORMAT ymd; with no influence on the convert(datetime,[value])- function.

Collectives™ on Stack Overflow

Converting string to datetime in yyyymmdd format

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related