Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values (2021-12-29)

SQLServerCentral Newsletter for December 29, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values]( - [A Day in the Life of a SQL Server Data Architect]( - [DBAs at work #3: The finance company Manager DBA]( - [From the SQL Server Central Blogs - Saving PowerShell Results to the Clipboard–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Understanding SQL Server IO Size]( Question of the Day - [Variant Math]( The Voice of the DBA  Daily Coping Tip Choose to give or receive the gift of forgiveness I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Change Approvals This editorial was originally published on Mar 24, 2017. It is being re-run as Steve is on vacation. Part of a developer's or DBA's career is getting their work released to some system where others can use the system. Some of us may do this more than others, but we all usually have some role, whether in packaging changes up or actually pushing the button that runs a script or copies files to some live server. This can be an exciting and stressful time, depending on how you feel about the quality of the work. Releasing software often isn't something done in a vacuum. Even in the highly agile, DevOps companies like Amazon and Facebook where developers release code many times a day, there is often some sort of approval process, whether implicit or explicit, before code goes out. Even if it's just a peer that code reviews something, or a business person that examines a test version and pronounces it correct, another person often weighs in on our changes. That's not always the case, as I know in emergencies, some of us make quick decisions and change or run code that only we have examined. I'd hope that's the exception, rather than the rule, with most database changes. Today I'm curious. Who approves your database changes? Is there a formal process? An informal one? Does the person making the decision even understand the code or do they depend on you to have written and tested solid T-SQL? It's been said that the person closest to the work is often the best person to judge if it should be released, but that's only partially true. Deploying code is often disruptive. It introduces change, which customers may or may not like. There may be good reasons to release at discrete intervals, rather than whenever the developer things things are working. This may change with heavy use of feature flags or feature toggles, but in general, code releases are interruptions and we want to limit them. Unless something is broken, in which case, we often want the change as quick as it can be released. Does that mean we want a different change process? Let us know today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values]( Greg Larsen from SQLServerCentral.com When you build applications that store data in SQL Server you will most likely have to store dates and times, and you’ll call functions to do date manipulations. It is important to understand the different date and time data types, and when to use one data type over another. In this level I will be exploring the different date and time data types and discussing when each type is appropriate. [External Article]( [A Day in the Life of a SQL Server Data Architect]( Additional Articles from MSSQLTips.com This article looks at what an average day of a SQL Server Data Architect looks like and some of the things you would need to as a Data Architect. [External Article]( [DBAs at work #3: The finance company Manager DBA]( Additional Articles from Redgate Want an insight into how your fellow DBAs work? This episode features Kevin Davis, Manager of Database Administration at Tower Loan, a consumer finance company with 250 branches across the US. [Blog Post]( From the SQL Server Central Blogs - [Saving PowerShell Results to the Clipboard–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Working with results and manipulating them is always... [Blog Post]( From the SQL Server Central Blogs - [Understanding SQL Server IO Size]( aen from Anthony Nocentino Blog This blog post shows you how NTFS stores data, what the NTFS Allocation Unit means, and how SQL Server performs IOs of variable size. How NTFS Stores Data on Disk...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Variant Math What does this code return? DECLARE @i SQL_VARIANT = 4; SELECT @i + 2 Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Amazon Redshift What type of database is Amazon Redshift? Answer: A cloud data warehouse service Explanation: Amazon Redshift is for cloud data warehousing Ref: Amazon Redshift - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [failure]( - There is a error in the application logs with table name not allowing nulls into column. However, the sql server error log does not show which proc is causing the error. Looked at monitoring tool did not find anything associated. Is there any other place to look into it. Do yo think the better option […] SQL Server 2017 - Development [Customized Schedule Job through TSQL]( - Hi All, After a long time posting my query. It would be great help if anyone can assist on this. I would to like to create one schedule for sql job which should run after every 1 week from the active start date.  For Example : If active start date of the job is […] Development - SQL Server 2014 [Count based on condition]( - Hi I have below query . In T1.Sample1,T1.Sample 'Y' & 'N' is stored . I want to display Count of T1.Sample1 which have & and similarly of T1.Sample2 SELECT T1.U_VendCode,T1.U_VendName as "Vendor Name",T1.Sample1,T1.Sample2 FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' ) group by T1.U_VendCode,T1.U_VendName,,T1.Sampl1,T1.Sample2 Code […] [ Error - Each GROUP BY expression must contain at least one column]( - Hi Error - Each GROUP BY expression must contain at least one column that is not an outer reference. SELECT T1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*), (Select Count(*) from [tbl2] where T1.U_APDE <> 'N' group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode), T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode FROM [tbl1] T0 inner join [tbl2] T1 on T0.Docentry = T1.DocEntry where (T1.U_ItemCode is […] [Inner select count(*) is not returning any data.]( - Hi Inner select count(*) is not returning any data. SELECTT1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*),(Select Count(*) from [tbl1] where U_APDE = 'Y'),T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCodeFROM [tbl0] T0inner join [tbl1] T1 on T0.Docentry = T1.DocEntrywhere NOT(T1.U_APDE = 'N' AND T1.U_ARDE = 'N') and (T1.U_ItemCode is not null) and (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' )and T1.U_CustCode […] SQL 2012 - General [Urgent help needed. MS SQL Server 2012]( - Hi, I've just got a call (6pm on a Friday!) from a customer who has had his line-of-business application provider sell him a solution which required SQL Server 2012 Standard, yet the supplier (joyfully) installed the evaluation version of SQL Server 2012 Enterprise. This has now ticked over and expired. My problem is that (a) […] SQL Server 2012 - T-SQL [how to make comm seprated list with control break. in sql 2012]( - hi, col "R" has report no like "R1" "R2" ETC COL "D" AS Direction like "D" "D1". direction can be more than one in one report. --uniqueness of record is col R + D result needed is all directions of the table IN one SCALAR column with all comma seprated and ";" seprated like FOLLOWING. […] SQL Server 2019 - Administration [Getting error Failed to open connection dialog when trying to open SSMS 18]( - I've got SSMS 18.3 installed on my laptop. When I open it, I'm getting an odd error I've never seen before: Failed to open connection dialog Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.SqlStudio) I cannot get past this, and I don't know how to fix it. How do I fix this […] SQL Server 2019 - Development [SQL Agent Job Use Different Environment Variable]( - Hi, Using SQL2019, we have multiple projects and packages in our SSISDB. They all reference a single shared Environment which holds our Connection strings. I have a package using a project level Connection. This package by default uses Connection1, so configuring the sql agent job is simple and this job runs weekly. I want to […] [SQL Filestream Retrieve File with VB or SQL]( - Hello, I want to use SQL Filestream to store files. I am a fairly novice developer and this is new. I have set up my database to store files and have successfully imported files with SQL and using Excel VB to execute the SQL. I would like to use Excel VB to export the file […] [copy rows in the same table based on conditions]( - I have the table as in data below , now I would like to copy all of the rows in which TEXT1 is 'erledigt' and the DATE2 is within the year 2021 , I would like to copy the rows within the same table and make sure to copy the same number of rows as […] [Passing a column into CONTAINSTABLE/CONTAINS]( - I am running a search, passing a column with keywords into inner joint. SELECT         SentimentCategoriesAncors._pk AncorID, Clip._pk ClipID FROM         SentimentCategoriesAncors INNER JOIN Clip ON Clip.title LIKE '%' + SentimentCategoriesAncors.incude + '%' I need get some speed and put some fuzzy logic in play. So, I indexed Clip.title into Full-Text Search and trying to do something […] [3 calculations on different levels]( - Hello everyone, I am asking something that I am not even sure is even possible therefore I will appreciate any suggestion or advice. I have a SQL code that works just fine. But I have an issue My calculations for Inventory On Hand and On Order are done using LOC column (it is basically a […] General Cloud Computing Questions [Noob questions about Cloud computing - What made it so prevalent?]( - We're seeing everything going to the cloud these days, from data storage to phone systems. I don't fully understand what technologies have made cloud computing so prevalent and easier to implement. Is it due to Internet bandwidth? Better software? Integration Services [Azure Data Studio for managing SSIS packages?]( - Hello all, I was wondering if anyone knows of a way to manage SSIS packages using Azure Data Studio? For example, I'd like the functionality of deploying packages: ...and checking on the results from packages that have run in the past: I've Googled and searched this forum but can't find any information on it. This […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -