Get Dynamic Clock In or clock time in SQL

-

 Now, I am going to generate dynamic time like real punch time for Clock In or Clock Out. When we implement punch time functionality.


Create a View for Get Random value in SQL. Because SQL not allow RAND() function in user define function. So I am creating a view for this

CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value

Create a user define function to get real punch time. I am passing date of punch and time range in time format.

GO
CREATE FUNCTION [dbo].[FnGetPunchTime]
(@WorkingDate DATETIME,@timeRangeBegin time,@timeRangeEnd time)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Result AS DATETIME;   
DECLARE @RandomTime  DATEtime, @TimeSpanInMSec int = datediff(MILLISECOND,@timeRangeBegin, @timeRangeEnd)
SELECT @RandomTime=DATEADD(MILLISECOND,(SELECT Value FROM vw_getRANDValue) *  @TimeSpanInMSec,@timeRangeBegin)
SELECT @Result=CONVERT(DATETIME, CONVERT(varchar, @WorkingDate, 23)   + ' ' + CONVERT(varchar, @RandomTime, 114))
    RETURN (@Result);
END


I am putting some data and get result using below script

DECLARE @TimeRangeBegin time = '7:45', @TimeRangeEnd time = '09:15',@WorkingDate date='2019-10-11';
select [dbo].[FnGetPunchTime](@WorkingDate,@TimeRangeBegin,@TimeRangeEnd) as PunchTime from (values(1),(2),(3),(4),(5),(6),(7)) Y(A)

Out put

Comments

Post a Comment

Popular posts from this blog

Form submit resulting in “InvalidDataException: Form value count limit 1024 exceeded.” In ASP.NET Core

-
You can change the default formvalue limit using the   FormOptions . If you are using MVC, then you can create a filter and decorate on action where you want to extend this limit and keep the default for rest of the actions. namespace Filter {     using System;     using Microsoft.AspNetCore.Http.Features;     using Microsoft.AspNetCore.Mvc.Filters;     /// <summary>     /// Request Form Size Limit Attribute     /// </summary>     /// <seealso cref="System.Attribute" />     /// <seealso cref="Microsoft.AspNetCore.Mvc.Filters.IAuthorizationFilter" />     /// <seealso cref="Microsoft.AspNetCore.Mvc.Filters.IOrderedFilter" />     [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]     public class RequestFormSizeLimitAttribute : Attri...
Read more

What is the importance of EDMX file in Entity Framework

-
Image
ADO.NET entity is an ORM (object relational mapping) which creates a higher abstract object model over ADO.NET components. So rather than getting into dataset, datatables, command, and connection objects as shown in the below code, you work on higher level domain objects like customers, suppliers, etc. DataTable table = adoDs.Tables[ 0 ]; for ( int j = 0 ; j < table.Rows.Count; j++) { DataRow row = table.Rows[j]; // Get the values of the fields string CustomerName = ( string )row[ " Customername" ]; string CustomerCode = ( string )row[ " CustomerCode" ]; }   Benefits of using EF The main and the only benefit of EF is it auto-generates code for the Model (middle layer), Data Access Layer, and mapping code, thus reducing a lot of development time. The importance of EDMX file in Entity Framework    EDMX (Entity Data Model XML) is an XML file which contains all the mapping details of how your objects map ...
Read more

Repository Design Pattern in C#

-
Image
  What is the Repository Design Pattern in C#? The Repository Design Pattern in C# Mediates between the domain and the data mapping layers using a collection-like interface for accessing the domain objects. In other words, we can say that a Repository Design Patternacts as a middleman or middle layer between the rest of the application and the data access logic. That means a repository pattern isolates all the data access code from the rest of the application. The advantage of doing so is that, if you need to do any change then you need to do in one place. Another benefit is that testing your controllers becomes easy because the testing framework need not run against the actual database access code. With a repository  design pattern introduced, the above figure can be changed to: Why we need   the Repository Pattern in C#? Most of the data-driven applications need to access the data residing in one or more other data sources. Most of the time data sources will be a d...
Read more