| Author |
 Topic  |
|
|
ashleys
Starting Member
Mauritius
4 Posts |
 Posted - 11/07/2012 : 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?
thanks
ashley |
|
|
arpana patil
Starting Member
India
24 Posts |
Posted - 11/07/2012 : 01:50:08
|
| is it in dd/MM/yyyy or MM/dd/yyyy format? |
 |
|
|
ashleys
Starting Member
Mauritius
4 Posts |
Posted - 11/07/2012 : 02:01:21
|
| dd/mm/yyyy |
|
|
|
arpana patil
Starting Member
India
24 Posts |
Posted - 11/07/2012 : 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
Mauritius
4 Posts |
Posted - 11/07/2012 : 02:05:52
|
| ok but the thing is the value is 03112012 not 03/11/2012. |
|
|
|
arpana patil
Starting Member
India
24 Posts |
Posted - 11/07/2012 : 02:07:50
|
declare @Date varchar(max)
set @Date='20121103'
SELECT convert(datetime, @Date, 103)
SELECT convert(datetime, @Date, 101) |
|
|
|
ashleys
Starting Member
Mauritius
4 Posts |
Posted - 11/07/2012 : 02:19:10
|
| thanks arpana. but why did you change it to yyyymmdd? |
|
|
|
andersqwe
Starting Member
United Kingdom
4 Posts |
Posted - 11/07/2012 : 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 |
|
|
| |
Topic |
|
convert varchar to date
