Wednesday, August 01, 2012 5:14 PMhello ,
I have a coloumn with nvarchar of the form '2009-01-06 04:30:00+01' this column has a 140.000 rows
i need to change it from nvarchar to small date time . I guess the easiest way is get rid of the +01 and then cast it
i have found many articles about around the net but cant put it alltogether.
Could someone tell me please how i could write the whole procedure that will cast this column ? Table has also 11 more colums.
thank you ,
Wednesday, August 01, 2012 5:19 PM
You could try something like this.
DECLARE @DATE1 AS NVARCHAR(40); SET @DATE1 = '2009-01-06 04:30:00+01'; SELECT CAST(LEFT(@DATE1,19) AS SMALLDATETIME)
Alternatively, you could try a substring function to find the '+01' and select anything to the left of it.
Wednesday, August 01, 2012 5:24 PM
Depends on the SQL Server version you're using (2008+):
DECLARE @Value NVARCHAR(255) = '2009-01-06 04:30:00 +01'; SELECT CAST(CAST(@Value + ':00' AS DATETIMEOFFSET) AS SMALLDATETIME);
Wednesday, August 01, 2012 5:26 PM
Better use CONVERT with style 120 here. Otherwise, the conversion is language-dependent. For example, try this code under British:
SET LANGUAGE British; DECLARE @DATE1 AS NVARCHAR(40); SET @DATE1 = '2009-01-06 04:30:00+01'; SELECT CAST(LEFT(@DATE1,19) AS SMALLDATETIME)
You get June 1st, 2009 instead of January 6th, 2009. Here's a language-neutral conversion:
declare @s as nvarchar(22) = N'2009-01-06 04:30:00+01'; select convert(smalldatetime, left(@s, 16), 120);I'm using 16 characters because SMALLDATETIME's precision is a minute.
Wednesday, August 01, 2012 11:08 PM
thanks a lot for your replies
but how will i make it work for every row?
Thursday, August 02, 2012 12:14 AMModerator
You can follow the example below:
SELECT SalesOrderID, StringDate= CONVERT(varchar, dateadd(ss,SalesOrderID/2, OrderDate), 120) INTO tempdb.dbo.DateConversion FROM AdventureWorks2012.Sales.SalesOrderHeader ORDER BY SalesOrderID; GO --(31465 row(s) affected) SELECT SalesOrderID, StringDate, [SmallDatetime]=CONVERT(smalldatetime, StringDate, 120) FROM tempdb.dbo.DateConversion ORDER BY SalesOrderID; GO DROP TABLE tempdb.dbo.DateConversion; GO /* SalesOrderID StringDate SmallDatetime .... 47555 2006-09-14 06:36:17 2006-09-14 06:36:00 47556 2006-09-15 06:36:18 2006-09-15 06:36:00 47557 2006-09-15 06:36:18 2006-09-15 06:36:00 47558 2006-09-15 06:36:19 2006-09-15 06:36:00 .... */
Warning: smalldatetime has limited range.
Datetime conversion article:
Kalman Toth SQL SERVER 2012 & BI TRAINING
- Marked As Answer by JamesYiModerator Tuesday, August 07, 2012 3:08 AM