StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POI'm having issues cast/converting the dw datepart of GetDate()[3] so I can find it in a string [1,3,5]
text
Body
copied!<p>I know the datepart is returned as an int. The string lives in WeeklyFreq column with nvarchar data type in the Alerts table.</p> <p>I've tried every variation of cast and convert, but to no avail. The Google has let me down as well.</p> <p>I'm missing something that must be very obvious here. Any help would be greatly appreciated.</p> <pre><code>SELECT * FROM Alerts WHERE DATEPART(dw, GETDATE()) IN (SELECT WeeklyFreq FROM Alerts WHERE Id = 1) </code></pre> <p>Thank you both very much for your input. The solution seems to be:</p> <pre><code>SELECT * FROM Alerts WHERE ((SELECT WeeklyFreq FROM Alerts WHERE Id = 1) LIKE N'%' + CAST(DATEPART(dw, GETDATE()) AS nvarchar(2)) + N'%') </code></pre> <p>I agree that storing the data in separate columns rather than in a string would be the proper solution, but circumstances don't allow for that at this juncture.</p> <p>How do I accept both of your answers as the best choice?</p> <p>The big picture looks like this:</p> <pre><code>CREATE PROCEDURE [dbo].[usp_QueueOrdersWeeklyBetween] AS DECLARE @Id int DECLARE @VendorId int DECLARE @SurveyId int DECLARE @DealerId int DECLARE @ItemId int DECLARE @Email nvarchar(255) DECLARE Transaction_Cursor CURSOR FOR SELECT Orders.Id, Orders.VendorId, Orders.SurveyId, Orders.DealerId, Orders.ItemId, Retailers.Email FROM Orders INNER JOIN Retailers ON Retailers.DealerId = Orders.DealerId INNER JOIN Alerts ON Alerts.VendorId = Orders.VendorId WHERE (Orders.Queued = 0) AND (Orders.SurveyQueued = 0) AND (Alerts.Occurs = 3) AND (DATEPART(hh, GETDATE()) BETWEEN Alerts.WeeklyDailyFreqEveryStart AND Alerts.WeeklyDailyFreqEveryEnd - 1) AND (Alerts.Alert1 = 1) AND (Alerts.WeeklyDailyFreq = 2) AND (Alerts.WeeklyFreq LIKE N'%' + CAST(DATEPART(dw, GETDATE()) AS nvarchar(2)) + N'%') OPEN Transaction_Cursor FETCH NEXT FROM Transaction_Cursor INTO @Id, @VendorId, @SurveyId, @DealerId, @ItemId, @Email WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO MailQueue (OrderId, VendorId, SurveyId, DealerId, ItemId, MailedTo, WhenQueued) VALUES (@Id, @VendorId, @SurveyId, @DealerId, @ItemId, @Email, GetDate()) UPDATE Orders SET Queued = 1 WHERE ID = @Id FETCH NEXT FROM Transaction_Cursor INTO @Id, @VendorId, @SurveyId, @DealerId, @ItemId, @Email END CLOSE Transaction_Cursor DEALLOCATE Transaction_Cursor GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload