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 |
|
ajt-systems
Starting Member
3 Posts |
 Posted - 2011-12-08 : 16:27:58
|
| Hi,I have a column in a table in the following format, it has been imported from a file:It's a datetime stamp2110201119580921 10 2011 19 58 09DD MM YYYY hh mm ssThe field in the DB is a text field, does anyone know a query to return it as a date format??Thanks peeps... |
|
|
Jeffreys
Starting Member
45 Posts |
Posted - 2011-12-08 : 16:42:42
|
| Well, the only way I know of to do it with that format string is by brute force as follows:declare @dtstr varchar(20)set @dtstr = '21102011195809'SELECT convert(datetime, STUFF(STUFF(LEFT(@dtstr,8),3,0,'-'),6,0,'-'), 105) -- date part+ convert(datetime, STUFF(STUFF(RIGHT(@dtstr,6),3,0,':'),6,0,':'), 108) -- time part |
 |
|
|
Jeffreys
Starting Member
45 Posts |
Posted - 2011-12-08 : 16:44:31
|
| ... essentially, you have to convert it to a format that the standard conversion formats recognize first. To do this you need to insert the delimeters between dd-mm-yyyy hh:mm:ss as follows. Then you can use the convert(datetime,sringval,format) |
|
|
|
|
|
|
|
|
Query for datetime