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 |
|
pwills
Starting Member
1 Post |
 Posted - 2002-11-17 : 11:56:42
|
| I need to convert a varchar(10) in a table that stores dates as 10102002 to a date format in the output of the query. The field is NextDate. |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-17 : 17:10:46
|
| for US date format useselect convert(datetime, substring(NextDate,1,2) + '-' + substring(NextDate,3,2) + '-' + substring(NextDate,5,4),101)and for the rest of the world useselect convert(datetime, substring(NextDate,1,2) + '-' + substring(NextDate,3,2) + '-' + substring(NextDate,5,4),103)PS in your example, you won't be able to tell the difference, so try 10112002 rather than 10102002.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-17 : 22:42:02
|
| If your string can be stored in the iso format yymmdd then you can also use SELECT CONVERT(DATETIME,NextDate,101) |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-17 : 23:15:39
|
| And what's more - that would be a much better way to store it...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-11-18 : 06:40:48
|
| This part: substring(NextDate,1,2) + '-' + substring(NextDate,3,2) + '-' + substring(NextDate,5,4) can be more concisely expressed as STUFF(STUFF(NextDate, 5, 0, '-'), 3, 0, '-') |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-18 : 18:18:00
|
| quite right - I'd never be one to quibble....--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
Date Formats