February 7, 2012 at 5:45 am
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
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
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
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
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
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
February 8, 2012 at 2:09 am
Hi John,
Thanks for your reply. The data types for these columns are datetime.
Thanks
Alex
September 5, 2018 at 12:55 am
select format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate
September 6, 2018 at 2:31 am
bolla.shravani - Wednesday, September 5, 2018 12:55 AMselect format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldate
Hi,
Did you notice this was a 6 year old thread?
September 6, 2018 at 3:48 am
nigel. - Thursday, September 6, 2018 2:31 AMbolla.shravani - Wednesday, September 5, 2018 12:55 AMselect format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldateHi,
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.
September 6, 2018 at 8:57 am
DesNorton - Thursday, September 6, 2018 3:48 AMnigel. - Thursday, September 6, 2018 2:31 AMbolla.shravani - Wednesday, September 5, 2018 12:55 AMselect format(convert(date,'2012-01-01'),'dd MMMM yyyy') as fulldateHi,
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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy