SQL date format functions like the DateDiff SQL function and DateAdd SQL Function are oft used by DBAs but many of us never took the time to fully understand these extremely useful features. For professionals just getting started with SQL Server, these functions are some of the first to become familiar with. So hopefully this article will have a little something for everyone across the skill spectrum
One of the most interesting data types that are supported in the relational database world is DateTime. In this article, we’re going to take a look at working with date time data types in SQL Server. We’ll understand the basics of date-time data-type and also, we’ll see various examples of how to query the date-time fields using built-in functions within SQL Server for manipulating the data, transforming date-time values and in few cases, perform arithmetic operations.
SQL Date functions
Posted in SQL
Tagged DateDiff, sql Date
This article on the SQL Delete is a part of the SQL essential series on key statements, functions and operations in SQL Server.
To remove a row from a table is accomplished through a Data Manipulation Language, aka DML statement, using the delete keyword. The SQL delete operation is by far the simplest of all the DML commands. On execution of the delete command, we don’t have to worry about getting any form of data from the table, and we don’t have to worry about working with any data that we get back from the table(s). We just simply tell the database to delete a specific record, and it either does or it doesn’t. It’s that simple.
First, let’s quickly review what an SQL delete statement looks like. We need to tell the database and table from where it should delete the data. It’s a good idea to add a condition clause to set the scope of data deletion. Otherwise, it will delete everything on the table.
Posted in SQL
Tagged SQL delete
This article on the SQL Insert statement is part of a series on string manipulation functions, operators and techniques. The previous articles are focused on SQL query techniques, all centered around the task of data preparation and data transformation.
The general format is the INSERT INTO SQL statement followed by a table name, then the list of columns, and then the values that you want to use the SQL insert statement to add data into those columns. Inserting is usually a straightforward task. It begins with the simple statement of inserting a single row. Many times, however, it is more efficient to use a set-based approach to create new rows. In the latter part of the article, let’s discuss various techniques for inserting many rows at a time.
The assumption is that you have the following the permission to perform the insert operation on a table
- Insert operation defaults to the members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner.
- Insert with the OPENROWSET BULK option requires a user to be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
- Download AdventureWorks2014 here
In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways to use PIVOT and UNPIVOT relational operators that can be used to transform aggregated distinct values as column(s) in the result-set by specifying all the column values in the PIVOT IN clause.
SQL Server pivot Introduction
There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.
SQL Pivot and Unpivot
The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.
The aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.
The requirement of data refactoring is very common and vital in data mining operations. In the previous article, you’ll learn the tips for getting started with SQL string functions, including the SQL substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.
After reading this article, you’ll understand more about:
- SQL String functions
- Understand the SQL Server SUBSTRING function
- How to handle data using the SUBSTRING SQL function
- How to use the SQL Server SUBSTRING function in the where clause
- How to dynamically locate the starting and end character position in the SQL Server SUBSTRING function
- How to work with date-time string using the SQL Server SUBSTRING function
- How to Create a simple sub-select using the T-SQL SUBSTRING function
- And more…
In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts.
In this guide, we’ll see the following topics:
- What is the data munging?
- How you can reduce your data preparation time
- How to easily get started with SQL string functions
- How to process data using SQL string functions
- And more…
We’ll look at specific SQL string function examples including
- SQL concatenate string
- SQL Server substring functions
- SQL string functions
- SQL Server convert string to date
- SQL replace string
- SQL convert INT to String
- SQL convert String to DateTime
- SQL string comparison
- And more …
This article is an effort to showcase the available SQL string functions to manipulate the raw data to make it more meaningful data-set for the data scientist to perform the data analysis using SQL Server.
This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.
In this article, I will explore various SQL tools. I will be discussing in detail about each of the following tools in the upcoming articles:
- SQL Operations Studio
Continue reading …
Getting started with SQL Server Tools
Microsoft has provided a platform and tools to manage modern data applications. The focus of this series is to help the DBAs to give the glimpse of currently available SQL Server DevOps tools.
- Sqlcmd – Overview of SQLCMD utility in SQL Server
- BCP – BCP (Bulk Copy Program) in Action
- SqlPackage – Continuous Deployment using SQL Server Tools SqlPackage.exe
- MSSQL-Scripter – SQL Server cross-platform MSSQL-Scripter scripting Tool
- SQL Operations Studio – Getting started with SQL Operations Studio (SOS)