Differences

This shows you the differences between two versions of the page.

--- tech-notes:sql [2021/05/14 11:39] – [6 Digit Random Number] gerardorourke
+++ tech-notes:sql [2022/12/21 14:06] (current) – [SUBSTRING and CHARINDEX] gerardorourke
@@ Line 34: / Line 34: @@
    as MaxIncomePerDay                                                     -- Pivot table alias
 where VendorId in ('SPIKE')                               -- Select only for this vendor
+</code>
+===== DateTime =====
+====Convert DateTime to Just Date====
+<code sql>
+CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102)
+</code>
+====Convert DateTime to Date + Hour====
+<code sql>
+Date = DATEADD(hour, DATEDIFF(hour, 0, DateTime), 0)
+</code>
+====Convert DateTime to Just the Year====
+<code>DATEPART(yy,[myDateTime])</code>
+====Convert DateTime to Just the Month====
+<code>DATEPART(mm,[myDateTime])</code>
+====Convert DateTime to the week ====
+<code>
+CASE WHEN DATEPART(week,[myDateTime]) = 1
+THEN DATEADD(day,-(DATEPART(dayofyear,[myDateTime])-1),DATEADD(day,DATEDIFF(day,0,[myDateTime]),0))
+ELSE DATEADD(day,-(DATEPART(weekday,[myDateTime])-1),DATEADD(day,DATEDIFF(day,0,[myDateTime]),0)) END
 </code>
@@ Line 53: / Line 77: @@
 ==CONVERT==
 http://msdn.microsoft.com/en-us/library/ms187928.aspx
-==Convert DateTime to Just Date==
-<code sql>
-CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102)
-</code>
-==Convert DateTime to Date + Hour==
-<code sql>
-Date = DATEADD(hour, DATEDIFF(hour, 0, DateTime), 0)
-</code>
@@ Line 263: / Line 277: @@
 https://alveenajoyce.wordpress.com/2020/03/31/how-to-generate-a-6-digit-random-number-in-sql-server/
-Ref: https://sqlperformance.com/2013/09/t-sql-queries/random-collisions
-<code sql>
-CREATE TABLE dbo.RandomNumbers
-(
-  RowID INT,
-  Value INT, --UNIQUE,
-  PRIMARY KEY (RowID, Value)
-);
-;WITH x AS
-(
-  SELECT TOP (1000000) s1.[object_id]
-  FROM sys.all_objects AS s1
-  CROSS JOIN sys.all_objects AS s2
-  ORDER BY s1.[object_id]
-)
-INSERT dbo.RandomNumbers(RowID, Value)
-SELECT
-    r = ROW_NUMBER() OVER (ORDER BY [object_id]),
-    n = ROW_NUMBER() OVER (ORDER BY NEWID())
-FROM x
-ORDER BY r;
-DELETE FROM [dbo].[RandomNumbers] Where Len(Value) <> 6
-</code>
-This query proves that there are no duplicate records
-<code>
-select Value , Count(*)
-from RandomNumbers
-Group By Value
-HAVING COUNT(*) > 1
-</code>
 ===== SQL - Querying Remote Data Source =====
   *https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/
@@ Line 328: / Line 308: @@
   *https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15
+===== SUBSTRING and CHARINDEX =====
+Here we have a column 'myColumn' with a comma separated value of (for example) 'apples,bananas' and we want FirstVariable = 'apples' and SecondVariable = 'bananas'
+<code>
+[FirstVariable] = SUBSTRING([myColumn],1,(CHARINDEX(',',[myColumn])-1))
+[SecondVariable] = SUBSTRING([myColumn],(CHARINDEX(',',[myColumn])+1),100)
+</code>
+CHARINDEX find the comma and lets you know the position it is in.
+SUBSTRING uses start and end position to extract a substring.