Post reply

Date conversion

  • July 31, 2009 at 6:22 pm

    #134355

    Hi all i have data in date column like

    jul 5 2009 12:00 AM but i need to have result like 07/05/2009 with no time.

    How can i do that.

    Thanks

  • July 31, 2009 at 7:32 pm

    #1033230

    What is the data type of the column involved?

  • July 31, 2009 at 7:55 pm

    #1033239

    As well as what the data type is, how are you looking at it?

    If it's type "datetime" and you run a SQL query like Select MyDate from MyTablein the management studio, you'll see one default representation, like "2009-07-05 00:00:00.000". If you select it into a variable then issue a print command like declare @mydate datetime

    select @mydate = MyDate from MyTable

    print @mydate you'll see another default format, similar to what you posted ("Jul 5 2009 12:00AM").

    To control this, you'll want to use the CONVERT() or CAST() functions. "Books On Line", also referred to as "BOL" is the help facility built into SQL Server. Start it from the same program group in which you find Management Studio, or click the Help menu or press F1 while in management studio. Look in the index for "Cast" or "Convert" The documentation there for these two functions will give you many choices for formatting a datetime field into a string.

  • July 31, 2009 at 8:10 pm

    #1033246

    pat (7/31/2009)

    Hi all i have data in date column like

    jul 5 2009 12:00 AM but i need to have result like 07/05/2009 with no time.

    How can i do that.

    Thanks

    What will you do with it after you get it formatted that way, Pat? I'm asking because it's generally a very bad idea to do this type of formatting in T-SQL to send to a GUI... it doesn't allow the local settings to take over the formatting when you do that.

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

  • July 31, 2009 at 9:54 pm

    #1033270

    Hi,

    I just need to get date on the report and the column is datetime format

    select tdate from table

    but the result i get back is Jul 5 2009 12:00

    but on the report i need 07/05/2009 with no time.

    Thanks

    Pat

  • August 1, 2009 at 12:01 am

    #1033283

    I thought most reporting programs had date formatters built in?

    No matter... here's how to do it in T-SQL... just keep in mind that it won't be "portable" from country to country because the formatting is hardcoded...

    SELECT CONVERT(CHAR(10),GETDATE(),101)

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

  • August 1, 2009 at 4:56 am

    #1033319

    Hi,

    Its better to use DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())) instead of the conversion.

    Because when you use this syntax fro order by, it will order t by date, while in VARCHAR, it will make it text comparison and then order it by.

    Thanks,

    Tejas Shah

    http://www.SQLYoga.com

    Tejas Shah

  • August 1, 2009 at 6:40 am

    #1033324

    Tejas Shah (8/1/2009)

    Hi,

    Its better to use DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())) instead of the conversion.

    Because when you use this syntax fro order by, it will order t by date, while in VARCHAR, it will make it text comparison and then order it by.

    Thanks,

    Tejas Shah

    http://www.SQLYoga.com

    It does help if you read the question. The OP wanted the date formatted for a report, not sorting. It would have been better to accomplish the formatting there, but that also isn't what the OP asked. Jeff's answer is actually the correct answer in this particular situation.

  • August 1, 2009 at 6:46 am

    #1033325

    Hi Lynn,

    I am totally agreed with you. Jeff answer is correct, as OP wants this for reporting purpose.

    SQL Reporting services, gives the facility to sort column. It is using the data sent back from SP. If there is a date field and converted to varchar, it will sort data by string. That's why I give my view to ride over that problem.

    I face this problem in one of my project's reports, so I keep practice to not convert the field.

    Convert is also an alternate way to accomplish the same. I just share my view.

    Thanks,

    Tejas Shah

    http://www.SQLYoga.com

    Tejas Shah

  • August 1, 2009 at 9:46 am

    #1033357

    Tejas Shah (8/1/2009)

    I face this problem in one of my project's reports, so I keep practice to not convert the field.

    I actually agree. Formatting dates and times in T-SQL is almost always the wrong thing to do and your formula for stripping the time off is one that is time honored and quick. I tend to be a true backend data troll and I write code that processes extremely large numbers of rows in each run using that little trick. But, like Lynn said, the OP need it to be formatted a certain way for display.

    That brings us to the next point (yours and mine) which is actually a question on my part... I don't do the formatting for reports... I just provide the high speed aggregations. As a result, I don't use SSRS so I don't know what it's capable of or not but it seems to me that a reporting tool like SSRS should be able to format a date for display just about anyway you want it. Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?

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

  • August 1, 2009 at 2:51 pm

    #1033400

    Tejas Shah (8/1/2009)

    Its better to use DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())) instead of the conversion.

    If the datepart you're using is Days, then the order does not matter, but if you are using any other datepart, you need to use the following.

    DateAdd(qq, DateDiff(qq, 0, GetDate()), 0) The reason is that the last parameter in the DateAdd function is the "date". So you need to convert 0 to a date rather than converting the interval to a date.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 1, 2009 at 10:21 pm

    #1033424

    That helped a lot all.

    Thanks

    Pat

  • August 2, 2009 at 10:58 am

    #1033452

    Heh... everybody is happy... 'cept me. 😛 No one answered my question. Here it is again...

    Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?

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

  • August 2, 2009 at 9:44 pm

    #1033519

    Jeff Moden (8/2/2009)

    Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?

    If all else fails, you can always do the conversion in the dataset. You can create a VB expression in SSRS using the FormatDateTime() function to format a datetime string as a general date, long date, short date, long time, or short time. If you want a short date, you can also use CStr().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 3, 2009 at 5:24 am

    #1033584

    Jeff Moden (8/2/2009)

    Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?

    Yes, it does.

    You use the field's Format property, which uses standard .NET format strings. See here

    Nigel
    How to post questions

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

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