Conversie mislukt bij het omzetten van datum en/of tijd uit tekenreeks bij het invoegen van datetime

Ik probeerde een tabel als volgt te maken,

create table table1(date1 datetime,date2 datetime);

Eerst probeerde ik waarden in te voegen zoals hieronder,

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

Het gaf een foutmelding,

Kan varchar niet omzetten naar datetime

Toen probeerde ik onderstaand formaat als een van de post voorgesteld door onze stackoverflow,

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)
                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

Maar ik krijg nog steeds de foutmelding,

Conversie mislukt bij het converteren van datum en/of tijd van tekenreeks

Iemand suggesties?

marc_s
Bewerkte vraag 2de2 januari 2013 в 8:52
Programmering
sql sql-server
2de2 januari 2013 в 8:49
40 views
Oplossing

Er zijn vele formaten die door SQL Server worden ondersteund - zie de MSDN Books Online on CAST and CONVERT. De meeste van die formaten zijn afhankelijk van welke instellingen je hebt - daarom werken die instellingen soms wel - en soms niet.

De manier om dit op te lossen is gebruik te maken van het (enigszins aangepaste) ISO-8601 datum formaat dat door SQL Server wordt ondersteund - dit formaat werkt altijd - onafhankelijk van uw SQL Server taal en dateformaat instellingen.

Het ISO-8601 formaat wordt ondersteund door SQL Server komt in twee smaken:

  • JJJMMDD voor alleen datums (geen tijdgedeelte); let hier op: geen streepjes!, dat'is erg belangrijk! JJJ-MM-DD is NIET onafhankelijk van de dateformaat instellingen in uw SQL Server en zal NIET in alle situaties werken!

of:

  • JJJ-MM-DDTHH:MM:SS voor datums en tijden - let hier op: dit formaat heeft streepjes (maar ze kunnen worden weggelaten), en een vaste T als scheidingsteken tussen het datum- en tijdgedeelte van uw DATETIME.

Dit is geldig voor SQL Server 2000 en nieuwer.

Dus in uw concrete geval - gebruik deze strings:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

en dan zou alles in orde moeten zijn (let op: je moet hiervoor het internationale 24-uurs formaat gebruiken in plaats van het 12-uurs AM/PM formaat).

Alternatief: als je op SQL Server 2008 of nieuwer zit, zou je ook het DATETIME2 datatype kunnen gebruiken (in plaats van gewoon DATETIME) en je huidige INSERT zou gewoon werken zonder enige problemen! :-) DATETIME2 is een stuk beter en een stuk minder kieskeurig bij conversies - en het'is sowieso het aanbevolen date/time datatype voor SQL Server 2008 of nieuwer.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine

Vraag me niet waarom dit hele onderwerp zo lastig en enigszins verwarrend is - dat'is gewoon zoals het is. Maar met het JJJMMDD formaat, zou het voor elke versie van SQL Server en voor elke taal en dateformaat instelling in uw SQL Server in orde moeten zijn.

B. Clay Shannon
Bewerkt antwoord 2de0 november 2015 в 6:32
Commentaren (3)

De conversie in SQL server mislukt soms niet vanwege de gebruikte Datum of Tijd formaten, Het is alleen maar omdat je probeert om verkeerde gegevens op te slaan die niet aanvaardbaar is voor het systeem.

Voorbeeld:

Create Table MyTable (MyDate);

Insert Into MyTable(MyDate) Values ('2015-02-29');

De SQL server zal de volgende foutmelding geven:

Conversie mislukt bij het converteren van datum en/of tijd vanuit tekenreeks.

De reden voor deze fout is simpelweg dat er geen datum (29 feb) is in het jaar (2015).

Commentaren (2)

Simpel antwoord - 5 is Italiaans "yy" en 105 is Italiaans "yyyy". Daarom:

SELECT convert(datetime,'21-02-12 6:10:00 PM',5)

zal correct werken, maar

SELECT convert(datetime,'21-02-12 6:10:00 PM',105)

zal een fout geven.

Ook zo,

SELECT convert(datetime,'21-02-2012 6:10:00 PM',5)

zal een fout geven, waar als

SELECT convert(datetime,'21-02-2012 6:10:00 PM',105)

zal werken.

Chankey Pathak
Bewerkt antwoord 7de2 juni 2017 в 5:34
Commentaren (0)