Post reply

to_char

  • August 20, 2009 at 8:47 am

    #213578

    Hi all,

    In oracle I use this query to format the date field. chg_dt is a date field in addchg table

    select to_char(chg_dt,'YYYY/DD/MM HH:MI') as date from addchg;

    DATECHG

    ----------------

    2002/11/11 12:00

    How can I do this in sql server?? Seems to be too complicated... I tried using convert but couldnt get this format

    select convert(datetime, chg_dt,112) from addchg

    I keep getting 2002-11-11 12:00:00:000

    Please help thanks

  • August 20, 2009 at 8:54 am

    #1042181

    Please see convert in BOL for other 'styles' of conversion


    Clear Sky SQL
    My Blog[/url]

  • August 20, 2009 at 9:09 am

    #1042192

    there's not a specific predefined format for the string you are looking for;

    two of the formats are pretty close, so by combining them you can get exactly what you want:

    declare @date datetime

    set @date=getdate()

    SELECT CONVERT(VARCHAR,@date,111) + ' ' + LEFT(CONVERT(VARCHAR,@date,114),5)

    --results:

    2009/08/20 11:09

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 20, 2009 at 9:13 am

    #1042194

    Thanks I got that working.

    But I've another problem. I have a column a with datatype varchar(19). If I insert a string with 15 characters it should automatically insert spaces to the left for 4 spaces..

    I tried using left() but, not sure how I can mention this in the insert..

    create table test1 (a varchar(19))

    insert into test1 values ('123456789123456')

    select left(' ' +a,19) from test1

    This inserts spaces when I select.. But how can I automate this if there are 16 characters to insert 3 spaces ..

    Thanks

  • August 20, 2009 at 9:21 am

    #1042202

    ok, i think you want to left pad the string so it is right aligned, correct?

    two ways I can think of, using either the SPACE or REPLICATE functions, and grabbing the RIGHT of the string.

    insert into test1

    select RIGHT(SPACE(19) + '456',19) UNION ALL

    select RIGHT(replicate(' ',19) + '876842',19)

    insert into test1

    select RIGHT(replicate(' ',19) + YourColumnName,19) From YourTable

    replicate is handy if you need preceeding zeros instead of spaces

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 20, 2009 at 9:24 am

    #1042206

    Thanks a million

  • August 20, 2009 at 7:03 pm

    #1042536

    As a bit of a sidebar... unless this formatting you want to do is strictly for output to a file, you should let the GUI do date formatting so that local "regional" settings will correctly format the date according to the region of the world the user is in. It also takes a bit of a load off the server. If the GUI is a report generator, let that do the formatting as well. For goodness sake, NEVER store formatted dates in the database because you will have to do conversions implicitly or explicitly to do even the simplest of date math in the future.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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