convert year part of a date

Hello all,
I have some data in a Varchar(15) column name datecolumn that I need to fix,
should be date value.
some of the data is like this
12/6/304
4/1/105
4/26/705
2/4/705
11/15/605
2/24/606
should be this:
12/06/2004
04/01/2005
04/26/2005
02/04/2005
11/15/2005
02/24/2006
thanks for your help
GVGV wrote:
> Hello all,
> I have some data in a Varchar(15) column name datecolumn that I need to fi
x,
> should be date value.
> some of the data is like this
> 12/6/304
> 4/1/105
> 4/26/705
> 2/4/705
> 11/15/605
> 2/24/606
> should be this:
> 12/06/2004
> 04/01/2005
> 04/26/2005
> 02/04/2005
> 11/15/2005
> 02/24/2006
> thanks for your help
>
Read up on SUBSTRING and CHARINDEX|||One solution by using parsename
create table Wasabi(CrappyDate varchar(20))
insert Wasabi values('12/6/304')
insert Wasabi values('4/1/105' )
insert Wasabi values('4/26/705' )
insert Wasabi values('2/4/705' )
insert Wasabi values('11/15/605')
insert Wasabi values('2/24/606')
select convert(datetime,'20'
+right(parsename(replace(CrappyDate,'/','.'),1),2)+
right('00' +parsename(replace(CrappyDate,'/','.'),3),2) +
right('00' +parsename(replace(CrappyDate,'/','.'),2),2))
from Wasabi
Denis the SQL Menace
http://sqlservercode.blogspot.com/
GV wrote:
> Hello all,
> I have some data in a Varchar(15) column name datecolumn that I need to fi
x,
> should be date value.
> some of the data is like this
> 12/6/304
> 4/1/105
> 4/26/705
> 2/4/705
> 11/15/605
> 2/24/606
> should be this:
> 12/06/2004
> 04/01/2005
> 04/26/2005
> 02/04/2005
> 11/15/2005
> 02/24/2006
> thanks for your help
> --
> GV|||If you need it in thr mm/dd/yyyy format then wrap the whole select in
convert,101
select convert(varchar(10),convert(datetime,'20
'
+right(parsename(replace(CrappyDate,'/','.'),1),2)+
right('00' +parsename(replace(CrappyDate,'/','.'),3),2) +
right('00' +parsename(replace(CrappyDate,'/','.'),2),2)),101)
from Wasabi
Denis the SQL Menace
http://sqlservercode.blogspot.com/
SQL Menace wrote:
> One solution by using parsename
> create table Wasabi(CrappyDate varchar(20))
> insert Wasabi values('12/6/304')
> insert Wasabi values('4/1/105' )
> insert Wasabi values('4/26/705' )
> insert Wasabi values('2/4/705' )
> insert Wasabi values('11/15/605')
> insert Wasabi values('2/24/606')
>
> select convert(datetime,'20'
> +right(parsename(replace(CrappyDate,'/','.'),1),2)+
> right('00' +parsename(replace(CrappyDate,'/','.'),3),2) +
> right('00' +parsename(replace(CrappyDate,'/','.'),2),2))
> from Wasabi
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> GV wrote:|||thanks for your help.... :< )
--
GV
"SQL Menace" wrote:

> One solution by using parsename
> create table Wasabi(CrappyDate varchar(20))
> insert Wasabi values('12/6/304')
> insert Wasabi values('4/1/105' )
> insert Wasabi values('4/26/705' )
> insert Wasabi values('2/4/705' )
> insert Wasabi values('11/15/605')
> insert Wasabi values('2/24/606')
>
> select convert(datetime,'20'
> +right(parsename(replace(CrappyDate,'/','.'),1),2)+
> right('00' +parsename(replace(CrappyDate,'/','.'),3),2) +
> right('00' +parsename(replace(CrappyDate,'/','.'),2),2))
> from Wasabi
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> GV wrote:
>sqlsql