Date formatting in a text string.

  • alex.newsum

    Valued Member

    Points: 57

    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

  • crazy4sql

    SSCoach

    Points: 19590

    does this link give you any help :-

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

    ----------
    Ashish

  • alex.newsum

    Valued Member

    Points: 57

    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! :/

  • crazy4sql

    SSCoach

    Points: 19590

    how about this one

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

    it gives me the result as 07 Feb 2012

    ----------
    Ashish

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    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

  • Cadavre

    SSC-Forever

    Points: 41582

    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
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    If you litter your database queries with nolock query hints, are you aware of the side effects?  Try reading a few of these links...
    (*) Missing rows with nolock
    (*) Allocation order scans with nolock(*) Consistency issues with nolock
    (*) Transient Corruption Errors in SQL Server error log caused by nolock
    (*) Dirty reads, read errors, reading rows twice and missing rows with nolock

    Craig Wilkinson - Software Engineer
    LinkedIn

  • alex.newsum

    Valued Member

    Points: 57

    Hi John,

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

    Thanks

    Alex

  • bolla.shravani

    Grasshopper

    Points: 11

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

  • nigel.

    SSChampion

    Points: 11477

    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

  • DesNorton

    SSC-Insane

    Points: 22440

    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.[/url]
    Make sure that you include code in the appropriate IFCode tags.

  • nigel.

    SSChampion

    Points: 11477

    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

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

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