Replacing Data – Part 2

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? In my last article, I created a inline table valued…

Replacing Data – Part 1

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? The Transaction SQL language does come with a REPLACE() string…

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,…