Convert Field


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Convert Field

  1. #1
    PhilipL Guest

    Convert Field


    I have a field in my table that has the data as '02272002'. I need to convert
    it to be like '02/27/02'. I tried running the following code in the Query
    Analizer and it errors. Can someone point me in the right direction. The
    Error says --- Invalid column name '/'.

    update tempdailypoints
    set salesdate = (Left('02272002',2) & "/"
    & Left('02272002',3) & "/"
    & Right('02272002',2))
    where Employee = '123'

    Thanks,
    PhilipL

  2. #2
    David Satz Guest

    Re: Convert Field

    try + instead of & :

    update tempdailypoints
    set salesdate = (Left('02272002',2) + "/"
    + Left('02272002',3) + "/"
    + Right('02272002',2))
    where Employee = '123'
    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions
    { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "PhilipL" <aplatfl@aol.com> wrote in message news:3c7d06eb$1@10.1.10.29...
    >
    > I have a field in my table that has the data as '02272002'. I need to

    convert
    > it to be like '02/27/02'. I tried running the following code in the Query
    > Analizer and it errors. Can someone point me in the right direction. The
    > Error says --- Invalid column name '/'.
    >
    > update tempdailypoints
    > set salesdate = (Left('02272002',2) & "/"
    > & Left('02272002',3) & "/"
    > & Right('02272002',2))
    > where Employee = '123'
    >
    > Thanks,
    > PhilipL




  3. #3
    PhilipL Guest

    Re: Convert Field


    "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >try + instead of & :
    >
    >update tempdailypoints
    >set salesdate = (Left('02272002',2) + "/"
    > + Left('02272002',3) + "/"
    > + Right('02272002',2))
    >where Employee = '123'
    >--
    >HTH,
    >David Satz
    >Principal Web Engineer
    >Hyperion Solutions
    >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS

    }
    >(Please reply to group only - emails answered rarely)
    >-----------------------------------------------------------------
    >"PhilipL" <aplatfl@aol.com> wrote in message news:3c7d06eb$1@10.1.10.29...
    >>
    >> I have a field in my table that has the data as '02272002'. I need to

    >convert
    >> it to be like '02/27/02'. I tried running the following code in the Query
    >> Analizer and it errors. Can someone point me in the right direction. The
    >> Error says --- Invalid column name '/'.
    >>
    >> update tempdailypoints
    >> set salesdate = (Left('02272002',2) & "/"
    >> & Left('02272002',3) & "/"
    >> & Right('02272002',2))
    >> where Employee = '123'
    >>
    >> Thanks,
    >> PhilipL

    >


    I am getting this error:

    Invalid column name '/'.

    Where do i correct it?



  4. #4
    David Satz Guest

    Re: Convert Field

    either SET QUOTED_IDENTIFIER OFF or change your "/" to '/'

    it thinks anything in double-quotes is a column
    you can also set this in QA under tools/options connection properties

    Dave
    "PhilipL" <aplatfl@aol.com> wrote in message news:3c7d2828$1@10.1.10.29...
    >
    > "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    > >try + instead of & :
    > >
    > >update tempdailypoints
    > >set salesdate = (Left('02272002',2) + "/"
    > > + Left('02272002',3) + "/"
    > > + Right('02272002',2))
    > >where Employee = '123'
    > >--
    > >HTH,
    > >David Satz
    > >Principal Web Engineer
    > >Hyperion Solutions
    > >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {

    VSS
    > }
    > >(Please reply to group only - emails answered rarely)
    > >-----------------------------------------------------------------
    > >"PhilipL" <aplatfl@aol.com> wrote in message

    news:3c7d06eb$1@10.1.10.29...
    > >>
    > >> I have a field in my table that has the data as '02272002'. I need to

    > >convert
    > >> it to be like '02/27/02'. I tried running the following code in the

    Query
    > >> Analizer and it errors. Can someone point me in the right direction.

    The
    > >> Error says --- Invalid column name '/'.
    > >>
    > >> update tempdailypoints
    > >> set salesdate = (Left('02272002',2) & "/"
    > >> & Left('02272002',3) & "/"
    > >> & Right('02272002',2))
    > >> where Employee = '123'
    > >>
    > >> Thanks,
    > >> PhilipL

    > >

    >
    > I am getting this error:
    >
    > Invalid column name '/'.
    >
    > Where do i correct it?
    >
    >




  5. #5
    PhilipL Guest

    Re: Convert Field


    "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >either SET QUOTED_IDENTIFIER OFF or change your "/" to '/'
    >
    >it thinks anything in double-quotes is a column
    >you can also set this in QA under tools/options connection properties
    >
    >Dave
    >"PhilipL" <aplatfl@aol.com> wrote in message news:3c7d2828$1@10.1.10.29...
    >>
    >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
    >> >try + instead of & :
    >> >
    >> >update tempdailypoints
    >> >set salesdate = (Left('02272002',2) + "/"
    >> > + Left('02272002',3) + "/"
    >> > + Right('02272002',2))
    >> >where Employee = '123'
    >> >--
    >> >HTH,
    >> >David Satz
    >> >Principal Web Engineer
    >> >Hyperion Solutions
    >> >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 }

    {
    >VSS
    >> }
    >> >(Please reply to group only - emails answered rarely)
    >> >-----------------------------------------------------------------
    >> >"PhilipL" <aplatfl@aol.com> wrote in message

    >news:3c7d06eb$1@10.1.10.29...
    >> >>
    >> >> I have a field in my table that has the data as '02272002'. I need

    to
    >> >convert
    >> >> it to be like '02/27/02'. I tried running the following code in the

    >Query
    >> >> Analizer and it errors. Can someone point me in the right direction.

    >The
    >> >> Error says --- Invalid column name '/'.
    >> >>
    >> >> update tempdailypoints
    >> >> set salesdate = (Left('02272002',2) & "/"
    >> >> & Left('02272002',3) & "/"
    >> >> & Right('02272002',2))
    >> >> where Employee = '123'
    >> >>
    >> >> Thanks,
    >> >> PhilipL
    >> >

    >>
    >> I am getting this error:
    >>
    >> Invalid column name '/'.
    >>
    >> Where do i correct it?
    >>
    >>

    >



    Thanks a lot that worked


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center