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 294: / 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.