cast nvarchar to smalldatetime

Question

hello ,
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 ,
Agis

Answer 1

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.

-Jordan

---

Jordan Johnson

Answer 2

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);

Answer 3

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.

---

-- BG

Answer 4

thanks  a lot for your replies

but how will i make it work for every row?

Answer 5

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:

http://www.sqlusa.com/bestpractices/datetimeconversion/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING