Post reply

Date formatting in a text string.

  • February 7, 2012 at 5:45 am

    #267956

    Hi All,

    I am not sure if I have put this in the correct section as am new to the forums... please move if not.

    I am using SQL Server 2005.

    My problem is dates within a text string... within a query. I have lots of separate queries used for pulling data from the SQL database to word templates etc.

    My query is below but want I would like to know is why the dates outside of a text string will pull in "01 January 2012" in word and dates inside a text string need a format given otherwise they pull in "Jan 01 2012 12:00AM".

    I need all dates to be in this "01 January 2012" format without the time. There is not a format code in help that I can set to show this, does anyone know how I can change this.

    I am not sure if this is enough info, if you need more then just shout please! Sorry if this is not helpful at all!

    OUTSIDE A TEXT STRING:

    SELECT EVENTDATE

    FROM CASEEVENT E

    JOIN CASES C ON E.CASEID = C.CASEID

    WHERE E.EVENTNO = 196

    AND C.IRN = :gstrEntryPoint

    INSIDE A TEXT STRING:

    select case when r.relationship is null

    then ''

    else Char(10)

    + 'TEXTTEXTTEXT' + cast(ce.eventduedate as char)+ 'TEXTTEXTTEXT'

    + cast(DateAdd(Month, -1, ce.eventduedate)as char) + 'TEXTTEXTTEXT'

    + Char(10) end

    from cases c

    join relatedcase r on c.caseid=r.caseid

    join caserelation cr on r.relationship=cr.relationship

    join cases c2 on c2.caseid=r.relatedcaseid

    join caseevent ce on ce.caseid=r.relatedcaseid

    where c.propertytype = 'P'

    and r.relationship = 'REC'

    and c2.countrycode = 'HK'

    and ce.eventno = 219

    and c.irn =:gstrEntryPoint

    Thank you

    Alex

  • February 7, 2012 at 7:14 am

    #1443755

    does this link give you any help :-

    http://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

    ----------
    Ashish

  • February 7, 2012 at 7:22 am

    #1443762

    Thanks for your post, unfortunately not as non of the date formats set by SQL code are the long "01 January 2012". But I do not understand why a date is automatically pulled in this long format with the dates outside the text query.

    If that makes sense! :/

  • February 7, 2012 at 7:26 am

    #1443767

    how about this one

    select convert(varchar(12),getdate(),106)

    it gives me the result as 07 Feb 2012

    ----------
    Ashish

  • February 7, 2012 at 7:29 am

    #1443768

    alex.newsum (2/7/2012)

    But I do not understand why a date is automatically pulled in this long format with the dates outside the text query.

    It might have something to do with the fact you're selecting from a different column in each case. What are the data types for the eventdate and eventduedate columns?

    John

  • February 7, 2012 at 7:34 am

    #1443771

    alex.newsum (2/7/2012)

    Thanks for your post, unfortunately not as non of the date formats set by SQL code are the long "01 January 2012". But I do not understand why a date is automatically pulled in this long format with the dates outside the text query.

    If that makes sense! :/

    It's due to implicit conversion.

    e.g.

    BEGIN TRAN

    DECLARE @yourDate DATETIME

    SET @yourDate = GETDATE()

    SELECT @yourDate, --Default datetime

    CAST(@yourDate AS CHAR), --Implicit conversion

    CONVERT(VARCHAR(12),@yourDate,106), --DD MMM YYYY

    RIGHT('0'+CONVERT(VARCHAR(2),DAY(@yourDate)),2)+SPACE(1)+DATENAME(month,@yourDate)+SPACE(1)+CONVERT(VARCHAR(4),YEAR(@yourDate)) --DD MMMM YYYY

    ROLLBACK

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 8, 2012 at 2:09 am

    #1444345

    Hi John,

    Thanks for your reply. The data types for these columns are datetime.

    Thanks

    Alex

  • September 5, 2018 at 12:55 am

    #2004468

    select format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate

  • September 6, 2018 at 2:31 am

    #2004650

    bolla.shravani - Wednesday, September 5, 2018 12:55 AM

    select format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate

    Hi,
    Did you notice this was a 6 year old thread?

    Nigel
    How to post questions

  • September 6, 2018 at 3:48 am

    #2004662

    nigel. - Thursday, September 6, 2018 2:31 AM

    bolla.shravani - Wednesday, September 5, 2018 12:55 AM

    select format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate

    Hi,
    Did you notice this was a 6 year old thread?

    Not to mention that this is a SQL 2005 thread, and FORMAT only appeared on SQL 2012.
    That said, the performance of FORMAT terrible.

    How to post data/code on a forum to get the best help.

  • September 6, 2018 at 8:57 am

    #2004724

    DesNorton - Thursday, September 6, 2018 3:48 AM

    nigel. - Thursday, September 6, 2018 2:31 AM

    bolla.shravani - Wednesday, September 5, 2018 12:55 AM

    select format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate

    Hi,
    Did you notice this was a 6 year old thread?

    Not to mention that this is a SQL 2005 thread, and FORMAT only appeared on SQL 2012.
    That said, the performance of FORMAT terrible.

    Yeah, that too 😛

    Nigel
    How to post questions

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

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