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.
| Author |
Topic |
|
ywb
Yak Posting Veteran
55 Posts |
 Posted - 2007-01-19 : 18:45:21
|
| Hi,I have a table that stores the profile of my users, and there's a nvarchar field called "favouriteDays". User can enter any text like so:CREATE TABLE #tblUserProfile (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL, favoriteDate nvarchar(100) NULL);INSERT INTO #tblUserProfile (username, favoriteDate) VALUES ('Peter', '25 Dec 1950');INSERT INTO #tblUserProfile (username, favoriteDate) VALUES ('Paul', 'July 4th');INSERT INTO #tblUserProfile (username, favoriteDate) VALUES ('Mary', '01/12/2002');INSERT INTO #tblUserProfile (username, favoriteDate) VALUES ('Simon', '');INSERT INTO #tblUserProfile (username, favoriteDate) VALUES ('Tom', 'Christmas');Now how do I create a temp table that contains all these users, and have an additional column of type DateTime that stores the favorite date if the favoriteDate field can be successfully converted to DateTime (otherwise leave empty)?Thanks,ywb |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-19 : 19:49:05
|
| you can trycase when isdate(favoriteDate) = 1 then convert(datetime,favoriteDate) else null endbut it's not guaranteed to work (and not just because of the misspelling of favorite).What date is 01/12/2002 for instance.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
CONVERT/CAST DateTime?