Post reply

date format

  • June 30, 2005 at 8:08 am

    #167703

    Hi

    Is it possible to change the date format of a column in an SQL Server database.  The column is actually a varchar in the format yyyy/mm/dd and I would like to change is to dd/mm/yyyy.

    Is it possible to do this for every entry in the column.

    Thanks everyone

    David

  • June 30, 2005 at 8:14 am

    #569820

    Why not keep it as a date?

  • June 30, 2005 at 8:16 am

    #569822

    I could easily change it to a date, but is it possible to change the format using SQL coding

    Thanks

  • June 30, 2005 at 8:19 am

    #569830

    If every entry is in this format try using SUBSTRING. 

    ELECT SUBSTRING( '2005/07/13', 9, 2) + '/' + SUBSTRING( '2005/07/13', 6, 2) + '/' + SUBSTRING( '2005/07/13', 1, 4) 

    I wasn't born stupid - I had to study.

  • June 30, 2005 at 8:21 am

    #569832

    Yes but answer this first, why would you keep a date in a text field?? Doesn't that seem wrong to you?

  • June 30, 2005 at 8:22 am

    #569833

    ELECT???

    Pasting problem Farrell?

  • June 30, 2005 at 8:28 am

    #569837

    I'm totally new to this SQL Server stuff, I'm having difficulty understanding the ELECT statement.

    What do the numbers 9, 2   and 6,2 mean?

  • June 30, 2005 at 8:30 am

    #569839

    ELECT is short for SELECT

    6 is the start position, 2 is the length to retrieve..

    Check SUBSTRING in the books online for more details.

  • June 30, 2005 at 8:48 am

    #569853

    I probably should have realised that was SELECT

    But that just selects the rows from the table, how would this be made into an update query

  • June 30, 2005 at 8:51 am

    #569856

    Update dbo.YourTable set DatCol = SUBSTRING(DatCol, 9, 2) + '/' + SUBSTRING(DatCol, 6, 2) + '/' + SUBSTRING(DatCol, 1, 4)

  • June 30, 2005 at 8:59 am

    #569862

    Hi thanks for getting back to me. The code I'm using is....

     

    Update dbo.CallLogRevenuesTEST set dateOnlyEnd = SUBSTRING( dateOnlyEnd, 9, 2) + '/' + SUBSTRING( dateOnlyEnd, 6, 2) + '/' + SUBSTRING( dateOnlyEnd, 1, 4)

    and I'm getting the following error......

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbo.CallLogRevenuesTEST'.

    Not sure what the problem is though

  • June 30, 2005 at 9:01 am

    #569864

    The tablename is wrong or you didn't specify the right owner (try changing to your user name if the table name is correct).

  • June 30, 2005 at 9:04 am

    #569867

    The table name is correct, but what is the owner/username?

  • June 30, 2005 at 9:07 am

    #569872

    Run this to get the answer :

    Select user_name(uid) as OwnerName from dbo.SysObjects where name = 'CallLogRevenuesTEST ' and XType = 'U'

    then replace dbo. with the results of the query.

    If you get no rows from this it's because you're not in the right db or that the table has been deleted without you being notified.

  • June 30, 2005 at 9:07 am

    #569870

    I have to actually head off now, but thanks for the help and I'll check these posts again tomorrow.

    Thanks for the help

    Dave

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply