I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE … Read more
I want to convert a string like this: ’10/15/2008 10:06:32 PM’ into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE(’10/15/2008 10:06:32 PM’,’MM/DD/YYYY HH:MI:SS AM’) This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems … Read more
like when I do SELECT [Date] FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY [Date] how can I specify the group period ? MS SQL 2008 2nd Edit I’m trying SELECT MIN([Date]) AS RecT, AVG(Value) FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY (DATEPART(MINUTE, [Date]) / 10) ORDER BY RecT changed %10 to / 10. is it possible to make Date output without … Read more
I’ve heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I’m saying correct? If so, am i likely to see large differences in performance by rearranging the order of … Read more
WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn’t get it to work. 7 Answers 7
I am involved in a data migration project. I am getting the following error when I try to insert data from one table into another table (SQL Server 2005): Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The source data columns match the data type and are within … Read more
I have a table listing people along with their date of birth (currently a nvarchar(25)) How can I convert that to a date, and then calculate their age in years? My data looks as follows ID Name DOB 1 John 1992-01-09 00:00:00 2 Sally 1959-05-20 00:00:00 I would like to see: ID Name AGE DOB … Read more
Suppose I have a table with a numeric column (lets call it “score”). I’d like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | number of occurrences ————————————- 0-9 | 11 10-19 | 14 20-29 | 3 … | … In this example … Read more
I was wondering if it’s possible to do something like this (which doesn’t work): select cast( (exists(select * from theTable where theColumn like ‘theValue%’) as bit) Seems like it should be doable, but lots of things that should work in SQL don’t 😉 I’ve seen workarounds for this (SELECT 1 where… Exists…) but it seems … Read more
This question also has the answer, but it mentions DB2 specifically. How do I search for a string using LIKE that already has a percent % symbol in it? The LIKE operator uses % symbols to signify wildcards. 5 Answers 5
