Sql Server stringa alla conversione di data

Voglio convertire una stringa come questa:

'10/15/2008 10:06:32 PM' 

nel valore DATETIME equivalente in Sql Server.

In Oracle, direi questo:

 TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') 

Questa domanda implica che io debba analizzare la stringa in uno dei formati standard e quindi convertire utilizzando uno di quei codici. Sembra ridicolo per un’operazione così banale. C’è un modo più semplice?

SQL Server (2005, 2000, 7.0) non ha alcun modo flessibile, o anche non flessibile, di prendere un datetime strutturato in modo arbitrario nel formato stringa e convertirlo nel tipo di dati datetime.

Con “arbitrariamente”, intendo “una forma che la persona che l’ha scritta, anche se forse non tu o io o qualcuno dall’altra parte del pianeta, considererebbe intuitiva e del tutto ovvia”. Francamente, non sono sicuro che ci sia un tale algoritmo.

Prova questo

 Cast('7/7/2011' as datetime) 

e

 Convert(varchar(30),'7/7/2011',102) 

Vedi CAST e CONVERT (Transact-SQL) per maggiori dettagli.

Esegui questo attraverso il tuo processore di query. Formatta le date e / o le ore in questo modo e una di queste dovrebbe darti quello che stai cercando. Non sarà difficile adattarsi:

 Declare @d datetime select @d = getdate() select @d as OriginalDate, convert(varchar,@d,100) as ConvertedDate, 100 as FormatValue, 'mon dd yyyy hh:miAM (or PM)' as OutputFormat union all select @d,convert(varchar,@d,101),101,'mm/dd/yy' union all select @d,convert(varchar,@d,102),102,'yy.mm.dd' union all select @d,convert(varchar,@d,103),103,'dd/mm/yy' union all select @d,convert(varchar,@d,104),104,'dd.mm.yy' union all select @d,convert(varchar,@d,105),105,'dd-mm-yy' union all select @d,convert(varchar,@d,106),106,'dd mon yy' union all select @d,convert(varchar,@d,107),107,'Mon dd, yy' union all select @d,convert(varchar,@d,108),108,'hh:mm:ss' union all select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)' union all select @d,convert(varchar,@d,110),110,'mm-dd-yy' union all select @d,convert(varchar,@d,111),111,'yy/mm/dd' union all select @d,convert(varchar,@d,12),12,'yymmdd' union all select @d,convert(varchar,@d,112),112,'yyyymmdd' union all select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)' union all select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)' union all select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)' union all select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)' union all select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)' 

In SQL Server Denali, sarai in grado di fare qualcosa che si avvicina a quello che stai cercando. Tuttavia, non è ancora ansible passare alcuna stringa di data strambo definita arbitrariamente e aspettarsi che SQL Server lo soddisfi. Ecco un esempio che usa qualcosa che hai postato nella tua risposta. La funzione FORMAT () e può anche accettare locales come argomento opzionale – è basata sul formato .Net, quindi la maggior parte se non tutti i formati di token che ci si aspetterebbe di vedere saranno lì.

 DECLARE @d DATETIME = '2008-10-13 18:45:19'; -- returns Oct-13/2008 18:45:19: SELECT FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss'); -- returns NULL if the conversion fails: SELECT TRY_PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME); -- returns an error if the conversion fails: SELECT PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME); 

Ti incoraggio vivamente a prendere più controllo e a disinfettare i tuoi dati. I giorni in cui le persone digitano le date usando qualsiasi formato che desiderano in un campo di testo libero dovrebbero essere ormai alle nostre spalle. Se qualcuno entra l’8/9/2011 è il 9 agosto o l’8 settembre? Se li fai scegliere una data su un controllo del calendario, l’app può controllare il formato. Indipendentemente da quanto cerchi di prevedere il comportamento dei tuoi utenti, scopriranno sempre un modo più semplice per inserire una data che non hai pianificato.

Fino a Denali, però, penso che @Ovidiu abbia il miglior consiglio finora … questo può essere reso abbastanza banale implementando la tua funzione CLR. Quindi puoi scrivere una custodia / interruttore per tutti i formati stravaganti e non standard che desideri.


AGGIORNAMENTO per @dhergert :

 SELECT TRY_PARSE('10/15/2008 10:06:32 PM' AS DATETIME USING 'en-us'); SELECT TRY_PARSE('15/10/2008 10:06:32 PM' AS DATETIME USING 'en-gb'); 

risultati:

 2008-10-15 22:06:32.000 2008-10-15 22:06:32.000 

Hai ancora bisogno di avere quell’altra informazione cruciale per prima cosa. Non è ansible utilizzare T-SQL nativo per determinare se il 6/9/2012 è il 9 giugno o il 6 settembre.

Per questo problema la soluzione migliore che uso è avere una funzione CLR in SQL Server 2005 che utilizza una funzione DateTime.Parse o ParseExact per restituire il valore DateTime con un formato specificato.

perché non provare

 select convert(date,'10/15/2011 00:00:00',104) as [MM/dd/YYYY] 

i formati delle date sono disponibili su SQL Server Helper> Formati data di SQL Server

Questa pagina contiene alcuni riferimenti per tutte le conversioni datetime specificate disponibili per la funzione CONVERT. Se i tuoi valori non rientrano in uno dei pattern accettabili, allora penso che la cosa migliore sia percorrere la rotta ParseExact.

Personalmente se ti occupi di formati arbitrari o totalmente off the wall, purché tu sappia cosa sono in anticipo o in futuro, usa semplicemente regexp per estrarre le sezioni della data desiderata e formare un componente data / datetime valido.

Se si desidera che SQL Server provi a scoprirlo, utilizzare solo CAST CAST (‘qualunque’ come AS datetime). Tuttavia, questa è una ctriggers idea in generale. Ci sono problemi con date internazionali che emergono. Quindi, come hai trovato, per evitare questi problemi, si desidera utilizzare il formato canonico ODBC della data. Questo è il numero di formato 120, 20 è il formato per solo due anni di cifre. Non penso che SQL Server abbia una funzione integrata che ti consente di fornire un formato specifico per l’utente. Puoi scriverne uno tuo e potrebbe anche trovarne uno se cerchi online.

Usa questo:

 SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm 

E fare riferimento alla tabella nella documentazione ufficiale per i codici di conversione.