0

I'm selecting a substring from one column to populate another column using:

select doc_id,imagevolume, Load_date = substring (location, 42, 10)
from [!DocImagedArchivedLocation] as datetime
where LOADDATE is null

This provides me with the data I want the load date but it is NVCHAR in format and comes out like this: 01_13_2004. I'm using this select statement as part of an update statement to populate the load date field but cannot seem to get the syntax right to select this substring and covert it to a date time field.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Michael Feuti
  • 11
  • 1
  • 3

2 Answers2

0

Since you say 2008, check out this post: Create a date with T-SQL

If you were using 2012 I would say, you need to use DATETIMEFROMPARTS and put in the different parts of the date into the function: http://msdn.microsoft.com/en-us/library/hh213233.aspx

OR (Update from comments)

select 
doc_id,imagevolume, 
Load_date = CAST(substring (location, 48, 4) + '-' + substring (location, 45, 2) + '-' + substring (location, 42, 2)) AS DATETIME) -- year-month-day
from [!DocImagedArchivedLocation] as datetime
where LOADDATE is null
Community
  • 1
  • 1
wilsjd
  • 2,178
  • 2
  • 23
  • 37
  • Thank you for you help but that won't give me what I'm looking for as the string is from one column. basically I want the substring from 'location' which comes out like '01_13_2004' to come out like '2004-01-13 00:00:00.000' – Michael Feuti Apr 03 '13 at 18:48
  • Okay, so why can't you just break it up into parts? (see update to answer) – wilsjd Apr 03 '13 at 19:20
  • I guess I could break it into parts but that just seems like an extra step. the CONCAT function is not supported in 2008R2 unfortunately. – Michael Feuti Apr 03 '13 at 19:38
  • It's not too bad of an extra step though and with data like that, you don't have any other options. – wilsjd Apr 03 '13 at 19:46
  • Updated the answer to be for 2008R2. – wilsjd Apr 03 '13 at 19:47
0

You have to do some additional manipulation; try something like

select doc_id,imagevolume, Load_date = substring (location, 42, 10),
Load_Date2 = REPLACE(substring (location, 42, 10), '_', '/')
from [!DocImagedArchivedLocation] as datetime
where LOADDATE is null

That'll convert it to a NVARCHAR format that SQL Server can implicitly convert to a date syntax. Lots of caveats, though; your data has to be clean, and your server must be set to expect dates in the same format as the file you're importing (US versus UK, for example).

Stuart Ainsworth
  • 12,792
  • 41
  • 46