convert year part of a date

22 Jun 2006 2:27 PM

GV

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

--
GV

22 Jun 2006 2:31 PM

Tracy McKibben

GV wrote:
Show quote

> 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
>

Read up on SUBSTRING and CHARINDEX

22 Jun 2006 2:40 PM

SQL Menace

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:
Show quote

> 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
>
> --
> GV

22 Jun 2006 2:45 PM

SQL Menace

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:
Show quote

> 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 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
> >
> > --
> > GV

22 Jun 2006 3:02 PM

GV

thanks for your help..... :<)

--
GV

Show quote

"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:
> > 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
> >
> > --
> > GV
>
>