[T-SQL] How To Get a row(date) for each month

---

• How To Get a row(date) for each month
• .sp_send_dbmail sending an XML File dashes in front of -<Label> ??
• Optimize SP
• String to date conversion
• Transaction Isolation Level with CTE
• Using between in where clause from subquery
• Conversion failed when converting from a character string to uniqueidentifier
• Converting nvarchar value to int
• Select full month number (with 0)

How To Get a row(date) for each month Posted: 12 Jun 2013 10:38 PM PDT Hi All, I need to get a minimum month and year from a table and then a row for every month/year up to today.How to I achieve that, here's the query I'm using below:Select MIN(DATENAME(MONTH, dtePostedToWebsiteDate)) as MinMonth ,MIN(Year(dtePostedToWebsiteDate)) as MinYear ,'' as [RowForEachMonth] --??From dtlVacancyPostAuditWhere bitPostToWebSite = 1Please help.ThanksTeee

.sp_send_dbmail sending an XML File dashes in front of -&lt;Label&gt; ?? Posted: 13 Jun 2013 12:43 AM PDT Hi using .sp_send_dbmail to email an xml fileNew at XML files, but my sample to recreate looks like this: <CLUB> <REFERRAL> <field1>6.6.11</field1> <field3>Ongoing</field3> File looks ok (so far) except I have dashes in front- <CLUB> - <REFERRAL> <field1>6.6.11</field1> <field3>Ongoing</field3> ThanksJoe

Optimize SP Posted: 12 Jun 2013 11:19 PM PDT Hi!Next, we need to unify SP 2 has a size pretty damn considerably. =) Let the following scenario: Sp [A]Sp [B] The Sp [A] has a SELECT with 10 tables with inner join and left. Both [A] as [B] are very complex and require a legal use of the server. Hence the question, I need a unified report of [A] and [B] .. How best to run? Thank you.

String to date conversion Posted: 12 Jun 2013 09:51 PM PDT Hi all experts,Is it possible in SQL Server to convert a string type to date like[code="sql"]DECLARE @OrgDate Nvarchar(50)='2013-06'select CAST(@OrgDate as DATE)[/code]The above is wrong. But i would like to know whether there is any way of doing this. Like in Oracle we have TO_DATE() function.

Transaction Isolation Level with CTE Posted: 12 Jun 2013 07:33 AM PDT How do you handle the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" with common table expressions? If I add that before the CTE, I get yelled at by SQL.[code="plain"]Msg 319, Level 15, State 1, Line 12Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.[/code]So I'm wondering, do I have to put it with a semi-colon – which runs ok – before the CTE or after the CTE and before the select statement? Does it matter?Thoughts or experiences?

Using between in where clause from subquery Posted: 12 Jun 2013 09:43 AM PDT I am trying to find out how to do the same thing I do in a join in a where clause.I have the following that works fine:[code]DECLARE @Client TABLE( ClientId int, ClientName varchar(100), ValidDate smalldatetime)DECLARE @ValidDates TABLE( ValidDateId int, StartDate smalldatetime, EndDate smalldatetime)INSERT @Client VALUES (1, 'Tom Jones', '02/15/2013')INSERT @Client VALUES (1, 'Larry Smith', '4/05/2013')INSERT @Client VALUES (1, 'Jerry Lowell', '07/25/2013')INSERT @ValidDates VALUES (1,'01/15/2013','03/30/2013')INSERT @ValidDates VALUES (2,'5/15/2013','08/30/2013')SELECT * FROM @Client clJOIN @ValidDates vdON cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate[/code]But I need to do this in a where clause, possible using a subquery but how do I use the "BETWEEN" there?Thanks,Tom

Conversion failed when converting from a character string to uniqueidentifier Posted: 12 Jun 2013 08:24 AM PDT Hello,I am trying to do an update based on a join condition and I am getting the following error:Conversion failed when converting from a character string to uniqueidentifierupdate PJ set PJ.efg=1 from abc pj join hackthis HAH on Pj.JID=hah.TId where pj.lss=0JID is a uniqueIdentifier and TID is an nvarchar(50). And I know its erroring out on the join condition because of 2 different datatypes.Is there a way that I convert/cast and fix this?Thanks

Converting nvarchar value to int Posted: 12 Jun 2013 05:45 AM PDT I've a column name called "Codes" and the datatype is nvarchar. The data looks like X200, 516, XD1, YTG, 24ZY, 40Y, 01DXNow, I just need the numeric values from these data. If the value is all letters then I just want to display it as 0.From the above example, I just want to see it as 200, 516, 1, 0, 24,40, 01Any help is much appreciated,Thanks in advance.

Select full month number (with 0) Posted: 28 Feb 2013 05:38 PM PST Hi,I use[code="sql"]DATEPART(month, myDate)[/code]or[code="sql"]MONTH(myDate)[/code]the resut is: 2 (if myDate is 2013.02.03). I would like return: 02Do you have any solution for this?Thans!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610