Date formatting in a text string.

Question

Post reply

Date formatting in a text string.

alex.newsum

SSC Enthusiast

Points: 197
More actions
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

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

does this link give you any help :-

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

----------
Ashish

alex.newsum SSC Enthusiast Points: 197 More actions · Answer 2

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 More actions · Answer 3

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: 148809 More actions · Answer 4

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: 41690 More actions · Answer 5

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/

alex.newsum SSC Enthusiast Points: 197 More actions · Answer 6

Hi John,

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

Thanks

Alex

bolla.shravani Grasshopper Points: 11 More actions · Answer 7

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

nigel. SSChampion Points: 11636 More actions · Answer 8

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

DesNorton SSC-Insane Points: 24516 More actions · Answer 9

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.

nigel. SSChampion Points: 11636 More actions · Answer 10

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

Date formatting in a text string.

Cookies on SQLServerCentral