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 |
|
ashleys
Starting Member
5 Posts |
 Posted - 2012-11-07 : 01:29:43
|
| hi everyone,hope you are fine. in my table, i have a field of value '03112012'. is it possible to convert that to date?thanksashley |
|
|
arpana patil
Starting Member
24 Posts |
Posted - 2012-11-07 : 01:50:08
|
| is it in dd/MM/yyyy or MM/dd/yyyy format? |
 |
|
|
ashleys
Starting Member
5 Posts |
Posted - 2012-11-07 : 02:01:21
|
| dd/mm/yyyy |
|
|
|
arpana patil
Starting Member
24 Posts |
Posted - 2012-11-07 : 02:01:32
|
| if it is dd/mm/yyyy then SELECT convert(datetime, '23/07/2009', 103)if mm/ddd/yyyy SELECT convert(datetime, '07/23/2009', 101) |
|
|
|
ashleys
Starting Member
5 Posts |
Posted - 2012-11-07 : 02:05:52
|
| ok but the thing is the value is 03112012 not 03/11/2012. |
|
|
|
arpana patil
Starting Member
24 Posts |
Posted - 2012-11-07 : 02:07:50
|
| declare @Date varchar(max)set @Date='20121103'SELECT convert(datetime, @Date, 103)SELECT convert(datetime, @Date, 101) |
|
|
|
ashleys
Starting Member
5 Posts |
Posted - 2012-11-07 : 02:19:10
|
| thanks arpana. but why did you change it to yyyymmdd? |
|
|
|
andersqwe
Starting Member
4 Posts |
Posted - 2012-11-07 : 20:15:31
|
| Because SQL doesn't recognise this string as a date due to there being no slashes identifying where day, month and year parts start you need to add them in I have done this using the substring function and adding the '/'s where appropriate, you could use format string but this is more readable for smaller examples of string manipulation. I then used 103 date format as per the third parameter of the convert function you may need to change this depengding on the region that you are in or developing for.It should be noted this isn't brilliantly efficient and you should probably be looking to translate this information out side of the database i.e. before the data is added to any tables however the below should work.select convert(date ,SUBSTRING('03112011',1,2) + '/' + SUBSTRING('03112011',3,2) + '/' + SUBSTRING('03112011',5,4), 103)Its me |
|
|
|
|
|
|
|
|
convert varchar to date