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 |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-01-22 : 05:31:43
|
| hello,not sure how to do this,I have one table where the dob is varchar, I am inserting the data into a table with the field datetime, if there are spaces in the first dob then it cannot convert to datetime, do I need to trim ? |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-01-22 : 05:41:57
|
| depends where the spaces are surely?Maybe you ought to use the REPLACE function like REPLACE(dob, ' ', '') to eliminate any white spaces in your dob field. You could then do SELECT DISTINCT ISDATE(dob) FROM myTable and you should get a single value of 1 for all your dobs.________________Make love not war! |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-01-22 : 07:10:11
|
| Careful there, what format is the date stored in? A trim would be a good idea, but since there is no TRIM function is SQL Server, you can use LTRIM(RTRIM(columnname)). SQL Server can accept dates in many formats, look up Books Online for more information.OS |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-26 : 06:51:21
|
| hello, sorry I've taken so long to get back on this one.the data is stored as a varchar to begin with, could look like :'26/01/2004'' 26/01/2004'' '' 'would LTRIM(RTRIM(DOB)) be used ? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-01-26 : 08:06:43
|
| You have three problems.1- The spaces: You could get rid of those with the LTRIM and RTRIM functions2- Blank strings: You need to ensure that those records are excluded. 3- Date format: Your dates are in dd/mm/yyyy format, not recognized by SQL Server. Use CONVERT(datetime, columnName, 103) to convert these character values into valid SQL datetime values. The 103 parameter specifies that the character string contains a date in dd/mm/yyyy format. Look in the Books online for more details.OS |
|
|
|
|
|
|
|
|
trim to import data ?