Post reply

Formatting a date field

  • February 19, 2009 at 11:16 am

    #131136

    I am trying to write a query to export data to a billing system. I need the first row to be a #Date of transactions. I have a rough query written but do not know how to format the date to be as

    #DD-Month Abbreviated-YY. Below is my query to pull this data. And the result set. My date for the first row needs to be

    #19-FEB-09

    select '#' + convert(varchar, dbo.date_format(getdate(), 'dd-mm-yy'))

    union all

    select

    'P' + ',' + ',' +

    ',' + ',' + ',' + ',' + ',' +

    employee_id + ',' +

    client_num + '.' + matter_num + ',' +

    convert(varchar, meter_end - meter_start - meter_wasted) + ',' +

    ',' + ','

    from master_trans

    where end_date > dbo.date_format(GETDATE(), 'YYYY-MM-DD 00:00:00')

    and job_type_id = 1

    and date_export is null

    and export_id is null

    Results are:

    #19-02-09

    P,,,,,,,3143,999000.000,8,,,

    P,,,,,,,3143,999000.000,1,,,

    P,,,,,,,3130,999000.000,18,,,

    P,,,,,,,5413,999000.007,38,,,

    P,,,,,,,3122,553502.002,2,,,

    P,,,,,,,3103,600600.000,2,,,

    P,,,,,,,3122,553501.002,3,,,

    P,,,,,,,3123,632800.000,2,,,

    P,,,,,,,5415,999000.000,2,,,

    P,,,,,,,5457,353500.034,258,,,

    P,,,,,,,3118,999000.000,20,,,

    P,,,,,,,3123,446700.042,10,,,

    P,,,,,,,5411,999000.000,3,,,

    P,,,,,,,5411,999000.000,1,,,

    P,,,,,,,5411,999000.000,1,,,

    P,,,,,,,5434,999000.000,2,,,

    P,,,,,,,5418,999000.000,167,,,

    P,,,,,,,3123,446700.042,15,,,

    P,,,,,,,5415,999000.000,1,,,

    P,,,,,,,5411,999000.000,10,,,

    P,,,,,,,5433,999000.000,4,,,

    P,,,,,,,3118,999000.000,22,,,

    P,,,,,,,3120,999000.000,16,,,

    P,,,,,,,3120,999000.000,5,,,

    P,,,,,,,3109,999000.001,89,,,

    P,,,,,,,3120,999000.000,19,,,

    P,,,,,,,3118,999000.000,53,,,

    P,,,,,,,1540,999000.000,2,,,

    P,,,,,,,5433,999000.001,8,,,

    P,,,,,,,3118,999000.000,6,,,

    P,,,,,,,3192,999000.003,5,,,

    P,,,,,,,5418,571200.012,11,,,

    P,,,,,,,3123,353600.000,3,,,

    P,,,,,,,3118,999000.000,2,,,

    P,,,,,,,5411,999000.000,7,,,

    P,,,,,,,5415,999000.001,24,,,

    P,,,,,,,5415,999000.000,3,,,

    P,,,,,,,3118,999000.000,6,,,

    P,,,,,,,3123,353600.000,35,,,

    P,,,,,,,3171,353600.231,14,,,

    P,,,,,,,3123,353600.000,1,,,

    P,,,,,,,5415,999000.001,4,,,

    Any assistance would be greatly appreciated.

  • February 19, 2009 at 11:28 am

    #946948

    Closest I have seen is Select CONVERT(nvarchar(30), GETDATE(), 6)

    Result set would be 19 Feb 09.

    Maybe someone else knows.

    -Roy

  • February 19, 2009 at 11:33 am

    #946957

    Thanks, this works great except I need the - between the dd-month-yy.

  • February 19, 2009 at 11:37 am

    #946961

    you need to use a "style" in your CONVERT statement

    From BOL:

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    and there's a table with all sort of different numbers to use for different styles

    declare @myDate datetime

    set @myDate = '2008-02-22'

    select convert(datetime, @myDate, 120)

    Edit: sorry, that's not quite what you wanted at all - see BOL for suitable style to apply

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • February 19, 2009 at 11:41 am

    #946967

    Roy Ernest (2/19/2009)

    Closest I have seen is Select CONVERT(nvarchar(30), GETDATE(), 6)

    Result set would be 19 Feb 09.

    Maybe someone else knows.

    This?

    Select replace(CONVERT(nvarchar(30), GETDATE(), 6),' ','-')

  • February 19, 2009 at 11:44 am

    #946970

    That is practical. I was able to give this solution to him, but I was afraid that I might be reinventing the wheel since there might be a build in format for that.

    -Roy

  • February 19, 2009 at 11:46 am

    #946973

    I got my last post completely wrong, but anyway - the below should give dd-mm-yyyy:

    declare @d datetime set @d = '2008-09-15'

    select @d

    select convert(varchar(20), @D, 105)

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • February 19, 2009 at 11:48 am

    #946976

    Thanks everyone. The following worked for me.

    select convert(varchar, dbo.date_format(getdate(), 'dd-mmm-yy'))

    This returned me the value 19-FEB-09, just like I needed.

  • February 19, 2009 at 12:32 pm

    #947031

    [font="Verdana"]The dbo.date_format() function you are calling is not (so far as I know) a standard piece of SQL Server functionality. It looks like a function that someone has developed for your database.

    Most of us have to use convert() to format dates. Instead you have a fancy-dancy function to do it. 😀

    I suspect you don't need the convert around it... it's likely that your function already returns a formatted varchar.

    [/font]

  • February 20, 2009 at 6:08 am

    #947434

    You may want to check out this blog post, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx about formatting using SQL Server.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 20, 2009 at 5:25 pm

    #948046

    Yes, you are correct. The original developer or our company's softwae has created this date function. However, this fancy dancy date format does require a convert otherwise the query will error out. Seems to me that the original developer could have and should have created this function so it could be used without the convert. Thanks for your links, I will check them out.

Viewing 11 posts - 1 through 10 (of 10 total)

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