Convert a string date to datetime, missing yr no!

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'

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

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]')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources