Don’t pass over synonyms

I am finally back in the writing saddle again with a spree of tidbits for the month of April 2015. These articles will focus on learning a simple TSQL command or technique. Today, I want to talk about how synonyms can be used to preserve backward compatibility objects while allowing for schema redesign in existing databases. The CREATE SYNONYM and DROP SYNONYM key words were added to the TSQL language in the 2005 version. The main purpose of the construct is to provide a simple name to reference a database…

Date/Time Functions – SWITCHOFFSET()

This is the last short articles or tidbits on Transaction SQL date/time functions. It has surely been a-lot of work to write 41 articles on every way to slice and dice dates, times and offsets. Today, I am going to talk about the SWTICHOFFSET() function. This function is useful when you want to convert time from one zone to another. The function takes a date/time offset variable as input as well as the new offset value. It returns a date/time offset variable in the new zone. The examples below convert…

Date/Time Functions – @@LANGUAGE

I can almost see the end of my writing of short articles or tidbits on Transaction SQL date/time functions. We already talked about functions like DATENAME() that returns the month or day of a given date/time variable as as string. How does this function know what to return when the default language is Russian? Today, I will be talking about @@LANGUAGE, a configuration function which returns the current session value and SET LANGUAGE, a set statement which changes the value for the current session (SPID). Last but not least, the…

Date/Time Functions – @@DATEFIRST

I am on the home stretch with my writing of short articles or tidbits on Transaction SQL date/time functions. We already talked about functions like DATEPART() that returns the day of the week as an integer and DATENAME() that returns the day of the week as text. But what is the first day of the week? The default for the US English language is 7, or Sunday. However, this setting differs between countries. Today, I will be talking about @@DATEFIRST, a configuration function which returns the current session value and…

Date/Time Functions – EOMONTH()

I linger on to the end with my writing of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes we want to know the start date and end date of each month. Before SQL Server 2012, you used to have to set the date to the first of next month. Then use the DATEADD() function to back date by one day to get the end of month date. Today, I will be exploring the…

Date/Time Functions – ISDATE()

I am tireless with my writing of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes data comes from external sources in a textual format. How do we know if the data is a valid date/time variable? Today, I will be exploring the ISDATE() function that takes a character string as input and returns an integer as output. A numeric 1 represents a valid date and a numeric 0 represents an invalid date. The…

DATEDIFF() – Sub-second Functions

I am a devoted in my creation of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to calculate the difference between two given dates using a particular unit of measure (date part) like milliseconds, microseconds, and nanoseconds. Today, I will be exploring the DATEDIFF() function. This function has 12 different input parameters that can be passed to change the resulting output. To summarize, the function takes a date part,…

DATEDIFF() – Time Related Functions

I am constant in my creation of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to calculate the difference between two given dates using a particular unit of measure (date part) like hours, minutes, or seconds. Today, I will be exploring the DATEDIFF() function. This function has 12 different input parameters that can be passed to change the resulting output. To summarize, the function takes a date part, a…

DATEDIFF() – Date Related Functions

I am unfluctuating in my creation of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to calculate the difference between two given dates using a particular unit of measure (date part) like day, week, month, quarter or year. Today, I will be exploring the DATEDIFF() function. This function has 12 different input parameters, units of measure (date part), that can be passed to change the resulting output. To summarize,…

DATEADD() – Sub-second Functions

I am a pertinacious in my creation of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to add or subtract a fixed number of years, months, or etc from a date time variable. Today, I will be exploring the DATEADD() function. This function has 13 different input parameters that can be passed to change the resulting output. To summarize, the function takes a date part, a offset number, and…