Trending December 2023 # Use Cases Of Mysql Timestamp Function # Suggested January 2024 # Top 19 Popular

You are reading the article Use Cases Of Mysql Timestamp Function updated in December 2023 on the website Katfastfood.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Use Cases Of Mysql Timestamp Function

Introduction to MySQL TIMESTAMPDIFF()

Mysql timestampdiff() is a one of the types of DATE function which is used to calculate the subtraction or the difference of two dates which might be of the same type or different.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Mathematically, It can also be written as below expression:

Timestampdiff = difference(datetime expression1-datetime expression2)

Where Both the datetime expression can be of different datatypes. One expression might be of datetime, and the second expression might be of date type, or both may consist of the same time, like date or datetime.

It is not always compulsory that both the datetime expression be of the same unit type. One might be a date, and another might be a datetime. The DATE type is mainly computed as a datetime with a default time written as’00:00:00′.

Syntax of MySQL TIMESTAMPDIFF() Function

Below is the syntax of the mysql timestampdiff() function:

TIMESTAMPDIFF (unit type, datetime expression1, datetime expression2);

Unit type, datetime expressions, and datetime chúng tôi returns an integer as a result.

The TIMESTAMPDIFF function returns the output as first datetime expression1- second datetime expression2; here, datetime expression1 and datetime expression2 are Dates or Datetime expressions.

The unit argument defines the unit of the outcome of (datetime expression1-datetime expression2), which is represented as an integer

Below are the units of timestampdiff():

Microsecond

Second

Minute

Hour

Day

Week

Month

Quarter and

Year

The smallest unit that we can calculate timestampdiff function is SECOND, and the largest is YEAR.

MySQL TIMESTAMPDIFF Function with Examples

MySQL timestampdiff function with examples is given below:

1. Days

Below is the example that returns a difference of two date values, 2023-03-01 and 2023-03-10, in days:

Query:

select timestampdiff(day,'2023-03-01', '2023-03-10') AS result;

Output:

Here, AS means Alias, which means it will give a name result to the column, showing the calculated result of the query.

2. Months

Below is the example that returns a difference of two date values, 2023-03-01 and 2023-10-1, in months:

Query:

select timestampdiff(month, '2023-03-01', '2023-10-1') AS result;

Output:

3. Minutes

Below is the example that returns a difference of two DATETIME values in minutes:

Query:

select timestampdiff(minute, '2023-01-03 10:10:00', '2023-01-03 10:30:00') AS result;

Output:

Note: TIMESTAMPDIFF reflects only the time part.

Query:

select timestampdiff(minute, '2023-05-18 10:00:00', '2023-05-18 7:45:41') AS result;

Since we have passed the minute as the unit argument, the query’s result calculates only the minute and returns 45 as output. But the actual output should be 45 minutes and 41 seconds. If we want the output to be in second, we must pass second in the unit argument.

Query:

select timestampdiff(second, '2023-05-18 10:00:00', '2023-05-18 7:45:41') AS result;

Output:

45 minutes 59 second = 45 x 60 + 41 (seconds) = 2741 seconds

4. Microseconds

Below is the example that returns a difference of two DATETIME values in microseconds:

Query:

select timsetampdiff(microsecond, '2023-02-01 10:30:27.00000', '2023-02-01 10:30:27.123456') AS result;

Output: 

Use Cases of MySQL Timestamp Function with Examples

Use cases of the mysql timestamp function are given below:

How to Calculate the Age of A Child with Timestampdiff() Function?

Firstly, Let’s create a table with the name child for illustration:

create table child( Roll_no. INT auto_increment PRIMARY KEY,name varchar(100) NOT NULL, dob DATE NOT NULL);

After creating a table, it’s time to insert some records.

Insert into child(name,dob) values(('aman', '1990-01-01'),( 'rahul', '1989-06-06'),( 'ashish', '1985-03-02'),( 'divya', '1992-05-05'),( 'kunal', '1995-12-01'));

We can know to calculate the age of each child in the child table:

select Roll_no, name, dob, timestamp(year,dob, '2023-01-01') age from child;

In the above query, we have calculated the child’s age as of 2023-01-01. Also, if we want to calculate each child’s present age, we can use the now() function as a third argument of the timestampdiff function.

select id, name, dob, timestamp(year,dob,NOW()) age from child;

Negative Output of The Query:

If the first datetime argument is greater than the second date-time argument, the query output will be a negative number.

select timestampdiff(day, '2023-04-22', '2023-04-4') AS result;

Output:

In the below example, we have taken the two different types of arguments

select timestampdiff(minute, '2023-12-31', '2023-03-22 23:15:59') AS result;

Output:

Calculate the Working Hours of Each Employee in the Second

For this, first, we will create a table named EmployeeEntry:

create table EmployeeEntry( ClockInTime datetime, ClockOutTime datetime, Info INT(11) AS (ABS(TIMESTAMPDIFF(second,ClockInTime,ClockOutTime))) )ENGINE=MyISAM;

We will now insert some records in the table EmployeeEntry:

insert into EmployeeEntry (ClockInTime, ClockOutTime) values('2023-07-22 9:30:00','2023-07-22 06:34:56'); insert into EmployeeEntry (ClockInTime, ClockOutTime) values('2023-12-11 10:00:00','2023-12-11 07:30:16');

To fetch all the records of the table, we will use a select statement

select * from EmployeeEntry;

Output:

Conclusion

In this article, we have learned about MySQL’s TIMESTAMPDIFF function, how to use it to solve our daily problems, and its different use cases. We can utilize other units in the timestampdiff function depending on our requirements. Two real-life scenarios where this function can be helpful to are when we need to calculate a person’s age or figure out an employee’s working hours.

Recommended Articles

We hope that this EDUCBA information on “MySQL TIMESTAMPDIFF()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading Use Cases Of Mysql Timestamp Function

Top 4 Use Cases Of 5G Iot In 2023

The number of IoT devices is expected to reach 75 billion by 2025. This ever-growing number of smart devices requires a fast communication protocol to improve the efficiency of the IoT ecosystem. 5G is the fifth generation of cellular networks, claimed to be capable of downloading 10 gigabytes per second, up to 100 times faster than the 4G network, that can enable consistent connectivity for smart devices.

In this article, we will look at why 5G is important for IoT, what benefits it offers, and look at some use cases.

Why is 5G important for IoT?

Figure 1: Sustained growth in interest in 5G-IoT. Source: Google Trends

There has been a growing interest in the idea of 5G-IoT communication (Figure 1). That may be due to the following factors:

Low latency makes for a constant and wide-ranged monitoring.

Network providers claim that the peak speed of 5G network is nearly 1GBps, with a latency of less than 30 milliseconds, which is 23 milliseconds faster than 4G. This will assist in a stable IoT monitoring of smart locks, CCTV cameras, and other monitoring systems.

5G allows for the simultaneous connection of multiple devices to the same network.

Providers claim that, at a minimum, 5G allows for the connection of 1 million devices simultaneously for every square kilometer. This has potential for a distributed coordination amongst robots, actuators, and drones in an IoT ecosystem.

5G will lead to lower costs across some industries:

Education:

Education is already expected to have a 12% increase in IoT expenditure. Universities and colleges are migrating their infrastructure online, trying to bring the classroom experience to the student’s room by a faster and more consistent connection.

Because the majority of education is estimated to go digital, students’ tuition should decrease, as they will, theoretically, no longer be asked to finance dining halls, rec-centers, shuttle services, etc.

Delivery:

Food and grocery deliveries will be more efficient and faster. Amazon has already started drone-delivery of goods. These remotely-controlled drones should be able to cover a wider radius with a 5G network.

Delivery systems of this nature will reduce cost as they are scalable, where not only is it possible to integrate additional drones into an established infrastructure, but it would also be cheaper than hiring labor.

Lastly, because drones are exhaust-free, they could be more environment-friendly than delivery guys in cars or on motorbikes.

With that being said, remote delivery systems are not without any emissions, as powering devices’ batteries and keeping the cloud computing systems running round-the-clock uses a lot of energy, which translates to heat, which then causes a rise in temperature. We have an article that discusses the environmental concerns regarding IoT cloud.

What are the use cases of 5G IoT integration?

Some of the use cases of 5G in IoT include:

1. Research and development

5G connectivity can now allow manufacturers to produce a host of new connected products and services that were unavailable before.

For example, locational virtual reality apps (e.g. Google maps) could offer a more immersive experience by being connected to a host of devices on the street (i.e. street lamps, parking meters, street signs, etc.) transferring real-time images to your VR device. You would no longer be “put” on a cardboard-looking street, but rather see a realistic, live version of it.

2. Smart cities

5G and IoT networks can enhance the welfare of citizens. A relatable scenario could be improved traffic management.

Using the long bandwidth that 5G offers, complementary with more sensors and smart devices could make traffic management more proactive. For instance, before rush hours, traffic lights can preemptively cycle in a way to avoid bumper-to-bumper congestion.

3. Transportation

A 5G-powered IoT ecosystem could reduce the number of driving accidents and fatalities.

The real-life location of the closest pedestrians to a moving vehicle could be shown on the car’s radar, hence alerting the driver of blind spots. This is particularly useful for situations when a kid, who is not in the driver’s field of vision because of, say, height, suddenly decides to run across the street. Having preemptive knowledge of the kid’s (or any pedestrian’s) presence in the area, the driver will at least be in a better position to react, even if not to completely avoid the hazard.

4. Healthcare

IoT in healthcare already exists. But coupled with a 5G connectivity, a growing number of patients will have better and more efficient access to IoT-connected wearable devices, which facilitates the sharing of their data with their physicians regardless of either one’s location.

In addition, remote surgery could also be a possibility, with surgeons remotely directing a robot in real-time to perform delicate surgeries on-premise.

For more on IoT

To learn more about the internet of things:

Finally, If you believe your business will benefit from an IoT solution, feel free to check our data-driven hub of IoT solutions and tools.

And we can guide you through the process:

He primarily writes about RPA and process automation, MSPs, Ordinal Inscriptions, IoT, and to jazz it up a bit, sometimes FinTech.

YOUR EMAIL ADDRESS WILL NOT BE PUBLISHED. REQUIRED FIELDS ARE MARKED

*

0 Comments

Comment

Top 5 Examples To Implement Of Sql Timestamp

Introduction to SQL Timestamp

Timestamp is a data type and function in Standard Structured Query Language (SQL) that lets us store and work with both date and time data values, usually without specified time zones. The timestamp has a storage size of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits. Some SQL databases allow for customization of a timestamp data type where we can specify a timezone for the timestamp so that every time the database is used in a different timezone, it shows and accepts the corresponding date and time. For example, in PostgreSQL, we have a “timestamptz” data type that takes care of time zone changes as well. Timestamptz data type also has a storage size similar to a timestamp of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In this article, we will be learning the functions and differences of “timestamp” and “timestamptz” data types with the help of a few examples.

Syntax and Parameters:

The basic syntax of “timestamp” data type in SQL is as follows :

Timestamp 'date_expression time_expression';

A valid timestamp data expression consists of a date and a time, followed by an optional BC or AD.

Timestamptz 'date_expression time_expression +/- time_zone_expression '; 2004-10-19 10:23:54; Examples to Implement of SQL Timestamp

Below are a few examples to understand SQL Timestamp data type:

Note: For the purpose of this article, we have used the PostgreSQL server.

Example #1

Getting to know important details of timestamp and timestamptz data type in SQL.

Code:

SELECT typname, typlen, typtype, typcategory FROM pg_type WHERE typname ~ '^timestamp';

Output:

Example #2

SQL query to illustrate the difference between timestamp and timestamptz data types.

Code:

SELECT TIMESTAMP '2023-03-23 00:00';

Output:

SELECT TIMESTAMPTZ '2023-03-23 00:00';

Output:

Example #3

Some examples to show functions related to timestamp data type in SQL. First, let’s start by converting or casting a given date into a timestamp format, as shown below.

Code:

SELECT '2023-03-23' :: timestamptz;

Output:

Suppose if we want to know the current timestamp, we can use the current_timestamp function as shown below. It will return the current timestamp, i.e. current date and time implicitly converted into the default timezone.

SELECT current_timestamp;

Output:

Further, we can also check the timezone we are currently working in by using the SHOW timezone statement below. This becomes very helpful when you have operations all over the world.

SHOW timezone;

Output:

Next, we can even change the current timezone to a different timezone using the SET timezone statement. Here, you may choose from a wide variety of timezones like ‘Asia/Kolkata’, ‘Europe/Zurich’, ‘US/Pacific’ etc. In this example, we have changed the timezone from ‘Asia/Kolkata’ to ‘US/Pacific’.

SET timezone = 'US/Pacific';

A few functions like EXTRACT in SQL let us extract a specific piece of information from the timestamp. For example, we can extract DAY, MONTH, YEAR, HOUR, MINUTE, SECONDS, etc., from the timestamp.

In the following examples, we have tried to extract DAY and MONTH from the timestamp.

SELECT EXTRACT(DAY FROM '2023-03-23 00:00':: timestamp);

Output:

SELECT EXTRACT(MONTH FROM '2023-03-23 00:00':: timestamp);

Output:

Example #4

SQL query to generate a series of timestamps in a specified timezone. In this example, we have generated a series of timestamps with a regular interval of 5 hours in two time zones, namely, ‘Indian Standard Time’ and ‘Pacific Standard Time. We can observe the difference between the two series by closely looking at the outputs.

Code:

SELECT generate_series( timezone('IST', '2023-03-23 00:00'::timestamp), timezone('IST', '2023-03-23 23:00'::timestamp), '5 hour'::interval );

Output:

SELECT generate_series( timezone('PST', '2023-03-23 00:00'::timestamp), timezone('PST', '2023-03-23 23:00'::timestamp), '5 hour'::interval );

Output:

Example #5

A practical example to illustrate the use of timestamp or timestamptz data type in SQL. Let’s take an example of a business case, where a multinational bank wants to wish its customers “Happy Birthday” based on the customer’s local time. The bank’s database has a list of customers with their date of births. So the customers’ table can be created something like this :

Code:

CREATE TABLE Customers ( customer_id int, customer_name varchar(255), city varchar(255), date_of_birth timestamptz );

Output:

After inserting the relevant information, the data in the table will look something like this:

Suppose we want to wish our customers in London who have their birthdays today i.e. ‘24th March 2023’ as per Indian Standard Time; we might be required to write a SQL query as shown below:

SELECT customer_name, date_of_birth FROM customers WHERE EXTRACT(DAY FROM date_of_birth) = '24' AND EXTRACT(MONTH FROM date_of_birth) = '03';

Output:

SELECT customer_name, date_of_birth FROM customers

Output:

Conclusion

SQL timestamp is a data type and function used to store and work with data values in date and time formats, sometimes along with time zones and AD/BCs. They are very helpful when we have operations all over the world.

Recommended Articles

We hope that this EDUCBA information on “SQL Timestamp” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

How To Use The Mysql Replace & Query Examples

Introduction to MySQL REPLACE

The MySQL REPLACE function is one of the string functions used to replace all existences of a substring within the main string to result in a new substring.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The MySQL REPLACE function includes three parameters. The first parameter represents the main string where the replacement will occur. The other two parameters specify the substring to be replaced within the first string and the new substring for replacement.

This task in MySQL makes a case-sensitive function implementation while the string replacement process.

Hence, MySQL allows us to replace an old string with the new one in a column of the database table to perform any handy search and replace the needed one with the existing record value.

Syntax

The MySQL REPLACE function is used with the following syntax:

REPLACE(Original_String, Substring1, Subtring2)

Original_String: This term denotes the main string in which the new one replaces the old string.

Substring1: This is the required occurrence of the substring to be replaced present in the original string.

Substring2: This is the required substring with the help of which a new substring can be replaced from the old one.

How does the REPLACE function work in MySQL?

First, Let us check a simple example to learn the implementation of REPLACEfunction in MySQL. The query is written as follows, where the original string is “VBN” and the substring “B” within “VBN” will be replaced by the new string “X”:

SELECT REPLACE("VBN HJB", "B", "X");

Result:

As per the above query, the valid expression is specified to replace the old string with the new one in the main string. The function executes to replace all the occurrences of the second argument in a specified string using the desired new one.

Thus, Using the REPLACE function in MySQL, we can effectively handle spelling mistakes in columns and perform searches for words that may not be accurate or valid. We can update the records by replacing specific characters with appropriate ones to obtain the desired results.

In this way, for a column value replacement, let us take the statement as follows:

UPDATE Person SET Person_Address = REPLACE(Person_Address,'Jaiput','Jaipur');

This above helps to find all the occurrences of a spelling error in the column of the address of the table person and updates it with the correct one. For the function, the first parameter defines the specified column name without quotes, and the other two denote the substrings, which are responsible for replacing each other to produce a new string.

We should know that if we apply quotes with the field column in the function like ‘Person_Address’, then. As a result, the values of that column will be updated by this ‘Person_Address’. This will cause a sudden data loss of that column in the table.

Note that MySQL REPLACE function will make logical sense only if there exists a Primary Key or a Unique key in the table database so that the function can determine a new row without going through duplicity to make a replacement using indexes of the table; otherwise, it will be corresponding to an INSERT statement.

Also, learn that this function does not upkeep regular expression. If we want to substitute any text string using a specific pattern, we need to use a user-defined MySQL function, i.e., UDF from an external library.

Examples of MySQL REPLACE

We will execute the below queries, which show various ways to use this MySQL REPLACE string function in a database table:

Let us take a basic example of using MySQL REPLACE function:

Code:

Output:

2. Correcting invalid String characters within a word

We are replacing the word ‘Learnint’, correcting a spelling error with the ‘g’ substring, and removing it.

Code:

SELECT REPLACE("Learnint","t", "g");

Output:

3. Replace the substring containing numbers

Let us suppose the below query to replace a specific number string:

Code:

SELECT REPLACE("477", "7", "9");

Output:

4. Replace string using the Column name of a table and SELECT Statement

Considering a sample table named Books, we will use the MySQL REPLACE function in the query to show the string exchange of column values having the substring within it.

Code:

select * from Books;

For example, the query is executed as below to display the result:

Code:

SELECT BookID,BookName,Language, REPLACE(Language, 'English', 'Eng') AS 'Replaced Language' FROM books;

Output:

5. Replace string Example With SELECT & WHERE statement

We are now using the MySQL REPLACE function for a column of the Books table using a SELECT statement and will interchange the book name column string having a substring within the value to a new substring applied to the search. Table Books:

Code:

select * from Books;

The query is below:

Code:

SELECT BOOKID, BOOKNAME, REPLACE(BOOKNAME,'Science','Sci') FROM Books2 WHERE BOOKNAME = 'Nuclear Science';

Output:

6. MySQL REPLACE function with the UPDATE statement

Code:

UPDATE TableName SET ColumnName = REPLACE(ColumnName, Substring1, Substring2) WHERE CondExpr;//condExprs: Conditional expression

For example, taking the sample table Person, we perform the below query:

Code:

UPDATE Books2 SET BOOKNAME = REPLACE(BOOKNAME,'Networking','Computer Network'); select * from Books2;

Output:

Code:

update Books set PRICE = '2000' where BOOKID = 107; select * from Books;

Output:

7. Case Sensitive Error

If we execute the below query in MySQL, then the result will be as follows:

Code:

SELECT REPLACE('APPLE', 'e', 'g');

Output:

In this way, The MySQL REPLACE function supports a case-sensitive search for a string that needs to be replaced, resulting in a new value. So, we must specify valid string expressions on which we want to perform the search and replacement. If not, the result will be the same as the original one.

Conclusion

This article taught us about the MySQL REPLACE function and how to exchange an old string with a new one.

Thus, the MySQL REPLACE function helps to substitute text in a table column for tasks such as interchanging the obsolete link, fixing any spelling error in the database records, etc.

Recommended Articles

We hope that this EDUCBA information on “MySQL REPLACE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

How To Use The Clng Function In Vba?

Excel VBA CLng

Excel VBA CLng function is used to convert an expression or a variable’s value to a long integer. Every variable which is used in any programming language has a certain data type. Each data type has some range limited to it which means up to how much values a variable can store. For an example a long data type can store from -2,147,483,648 and 2,147,483,647. This is the limit of the Long data type. There are many instances when numerical data or value is assigned to a non-numerical data type or variable. In such scenarios we encounter errors. But in Excel VBA CLng is one such function that allows us to convert or change these values to the desired data type.

VBA CLng Function Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Now this expression can be a value or a variable or it can be any expression. The use of this function is to convert that expression or value to long integer but there are certain scenarios where we may encounter errors while using this function. Let us discuss how we use this function in examples and then we will discuss the possible errors we might encounter while using this function.

How to Use the CLng Function in VBA?

We will learn how to use the CLng function using the VBA code in Excel.

You can download this VBA CLng Excel Template here – VBA CLng Excel Template

Example #1

Let us begin with the first example for the CLng function. Let us start with the basics first and see how this function works. Follow the steps below to see how exactly the VBA CLng function works.

Step 2: Insert a new subprocedure.

Sub

Example1()

End Sub

Step 3: Let us just declare some variable as long.

Code:

Sub

Example1()

Dim

value

As Long

End Sub

Step 4: We can assign this variable a value using the CLng function.

Code:

Sub

Example1()

Dim

value

As Long

value =

CLng(

35150.45

)

End Sub

Step 5: So now we can use the Msgbox function to display the result.

Code:

Sub

Example1()

Dim

value

As Long

value =

CLng(

35150.45

)

MsgBox value

End Sub

Step 6: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

Example #2

Earlier we talked about how we sometimes declare a variable with non-numeric data type but assign a numeric value. Let us try it in this example. Follow the steps below.

Step 1: Let us declare our second example subprocedure.

Code:

Sub

Example2()

End Sub

Step 2: Declare two variables, one as a string and another as long.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: We can see that we have a variable as a string and let us assign a numeric value.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789"

End Sub

Step 4: Now we can CLng function to convert it to long.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789" newnum =

CLng(

num

)

End Sub

Step 5: We can use the Msgbox function.

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 6: Let us execute the code by hitting the F5 or Run button.

Example #3

We also discussed above that we might encounter some errors while using this function. Let us go through a couple of examples on how we can encounter some possible errors.

Step 1: In the same module let us declare another subprocedure.

Code:

Sub

Example3()

End Sub

Step 2: Let us again declare two variables one as a string and another as long.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: Now let us assign a string value to the string variable and then try to convert it to long using the CLNG function.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

End Sub

Step 4: We can use mgsbox function to check if we get any result.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 5: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

We received type mismatch because we tried to convert a string value to long.

Example #4

Step 1: In the same module let us declare another subprocedure.

Code:

Sub

Example4()

End Sub

Step 2: Let us again declare two variables one as a string and another as long.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: Now let us assign a value greater than long can hold to the string variable and then try to convert it to long using the CLng function.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

End Sub

Step 4: We can use Mgsbox function to check if we get any result.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 5: When we execute the above code by hitting the F5 or Run button we will encounter the following error.

We have encountered this error because the value was greater than the long data type can hold.

Explanation of CLng in VBA

As we discussed above how we use CLng function to convert an expression or a value to Long data type value. However, we also saw that in the process we encounter some errors when we provide data that is non-numeric or the data or value is out of the range of long data type.

Things to Remember

There are few things which we need to remember about CLng in VBA and they are as follows:

CLng is a function in VBA which is used to convert a value to a long data type.

This function has a single argument as an input.

While using this function we should consider in mind the range of long data type which is -2,147,483,648 and 2,147,483,647.

This function is used as an expression.

Recommended Articles

This is a guide to the VBA CLng. Here we discuss how to Use the CLng Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Excel Vba Sleep Function With Examples?

VBA Sleep Function

The sleep function in VBA is a Windows function. It is similar to the wait function in VBA. It is used to slow down or pause or we can say halt the running of a specific code by some specified time. Sleep function needs to be called in VBA while declaring it in the code. How we do that is what we will learn in today’s topic.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

As explained above, VBA Sleep is a Windows function and is present in the kernel database of Windows. The method of declaring and calling sleep functions in VBA differs for both 32-bit and 64-bit operating systems. It is a Windows API function.

The syntax to use the VBA Sleep function is as follows:

Sleep (Time in Mili Seconds)

So if we need to slow down or halt the code for 1 sec, we need to write the code as:

Sleep 1000

1000 is the mili seconds equal to 1 second and will slow down the code for 1 sec. If we want to slow down the code for 5 seconds, the code will be:

Sleep 5000

The declaration for sleep function is as follows:

#If VBA7 Then ' Excel 2010 or later Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr) #Else ' Excel 2007 or earlier Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long) #End If

Note: Before using the sleep function, there is one thing we need to keep in mind. We need to use this function in modules, not in the objects of Excel. To use VBA to ensure that the developer’s tab is turned on from the files tab in the options section.

How to Use Excel VBA Sleep Function?

We will learn how to use a VBA Sleep function with a few examples in Excel.

You can download this VBA Sleep Excel Template here – VBA Sleep Excel Template

VBA Sleep Function – Example #1

What we are going to do in this example is we will pop up a message to the user that the macro will stop for five seconds. And exactly after five seconds, we want a second message to pop up which says macro resumed.

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Use the declaration statement to use the sleep function. As I am using Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample()

End Sub

Step 5: Use the Mgsbox function to display the message that the macro will be paused for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds"

End Sub

Step 6: Use the Sleep function to pause the macro for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds" Sleep 5000

End Sub

Step 7: Now, use the msgbox function to display the message that the macro has been resumed.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds" Sleep 5000 MsgBox "Macro has been resumed"

End Sub

Step 8: Run the code from the run button provided or press F5 to see the result. We see the first message is displayed.

There was a pause for five seconds between both messages.

VBA Sleep Function – Example #2

Now what we are going to do in another example is that I have four variables A, B, C, and D. First, I want to add the value of A and B and display it, and after 5 seconds, I want to display the value of the addition of A, B, C, and D.

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Now, use the declaration statement to use the sleep function. As I am using Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

End Sub

Step 5: Declare six variables A, B, C, D, X, and Y to store values.

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

End Sub

Step 6: Give Random Values to A, B, C, and D.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25

End Sub

Step 7: Store the value of A + B in X.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B

End Sub

Step 8: Display the value of X.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X

End Sub

Step 9: Now, use the sleep function to pause for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X Sleep 5000

End Sub

Step 10: Now, in variable Y, store the value of X +C + D and display it.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X Sleep 5000 Y = X + C + D MsgBox Y

End Sub

Step 11: Run the above code from the provided run button or by pressing the F5 key to see the result. We see the first message is displayed as.

Step 12: Press OK and the macro waits for five seconds and displays the next result.

VBA Sleep Function – Example #3

In this example, we want to rename two worksheets, sheet 1 and sheet 2, as Anand and Aran, respectively. But the time duration between both should be five seconds. We want the macro to pause after renaming sheet 1 and then rename sheet 2. Currently, both sheets are named as follows:

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Now, use the declaration statement to use the sleep function. As I am using the Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2()

End Sub

Step 5: Activate worksheet 1 and rename it by the following code:

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed"

End Sub

Step 6: Use the sleep function to use delay for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed" Sleep 5000

End Sub

Step 7: Now rename sheet 2 with the following code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed" Sleep 5000 Worksheets("Sheet2").Activate Worksheets("Sheet2").Name = "Aran" MsgBox "Sheet 2 renamed"

End Sub

Step 8: Now run the code and see the first message displayed.

Also, we can check that sheet 1 is renamed.

Step 9: Press ok and wait five seconds for the next message and second sheet to be renamed.

The second sheet is also renamed.

Things to Remember

VBA Sleep is a window function, so to use it, we need to use declaration statements.

There are different declaration statements for different types of operating systems.

Simply using VBA Sleep freezes the macro for the time duration provided.

The time parameter given to the VBA sleep function is in milliseconds.

Recommended Articles

This is a guide to VBA Sleep Function. Here we discuss using Excel VBA Sleep Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Update the detailed information about Use Cases Of Mysql Timestamp Function on the Katfastfood.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!