Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert a string date to datetime, missing yr no!

Author  Topic 

PeteLeHoq
Starting Member

37 Posts

Posted - 2014-11-25 : 11:44:21
I have a table with a 25,000 records with fields like this:

Jan 1 196
Jun 29 195
Jan 22 196
Sep 17 195
Mar 22 193
Nov 3 197

Is there a quick way to add the zero onto the year, before I convert them into datetime format?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 11:52:44
update table
set field = field + 'o'
Go to Top of Page

PeteLeHoq
Starting Member

37 Posts

Posted - 2014-11-25 : 12:16:52
Sorry forgot to mention they are mixed in with other records, like this:

Oct 29 1954 12:00AM
May 13 1964 12:00AM
10/28/2012
05/30/1971
Dec 29 1961 12:00AM
03/19/1961
Jan 29 195
Jun 16 197
11/28/1969
06/24/1981
11/30/1960
10/27/1953
Feb 28 195
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 12:26:55
Then, you'll need a WHERE clause with the appropriate guards. Basically you want rows that end with 3 digits (LIKE '%[0-9][0-9][0-9]') but not 4 (NOT LIKE '%[0-9][0-9][0-9][0-9]')
Go to Top of Page
   

- Advertisement -