Problem In Inserting Date In Sql Server 7 Through Insert Query
Jul 20, 2005
hello myself avinash
i am developing on application having vb 6 as front end and sql server 7
as back end.
when i use insert query to insert data in table then the date value of
that query is going as 01/01/1900
my query is as follows
Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)
in above query though i used cdate to voucherdate value still it save in
database as 01/01/1900 though here it shows right date
plz help me its a very big issue for me & i really just fed of this
problem
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row :
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())� WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think.
I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).
The code for creating the table is here:- USE [CDSExtractInpatients6.2] GO /****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/ SET ANSI_NULLS ON GO
[code]...
I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.
i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.
I need an SQL string that inserts the current date into a database. So far I have tried: SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')" mycomm = New SqlCommand(sql, myconn) mycomm.ExecuteNonQuery() However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from ''' set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote' set @params = ''' with (firstrow = 2 , datafiletype = ''widechar'' , fieldterminator = '';'' , rowterminator = '' '' , codepage = ''1252'' , keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
Hi, The following INSERT query works in all aspects apart from the date value: String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text); The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900"). In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT. What am I doing wrong? Thanks very much. Regards Gary
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you. Query:
Drop table AccountReceivable GO --BEGIN TRANSACTION Create table AccountReceivable ( AccountRecID int identity (1,1) not null, PatientID int not null, PresentCharges int default 0 not null, PaymentMade money default 0 not null, PreviousBalance money default 0 not null, BalanceDue money default 0 not null, LastPaymentDate datetime not null, PresentDate datetime default GetDate() not null ) GO ALTER TABLE AccountReceivable ADD CONSTRAINT PK_AccountRecID Primary Key (AccountRecID) GO
ALTER TABLE AccountReceivable ADD CONSTRAINT FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID) GO --COMMIT --query to find delinquent accounts --DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table DELETE AccountReceivable GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate ) VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (918035,109,109,0,0,3/6/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,279,89,0,190,5/9/2005,5/9/2005) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,0,90,190,100,5/9/2005,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT) GO
How to insert date to the sql server database.I am getting input from the HTML form and store it to database usingASP.how to store date field, what datatype needed and what conversionneeded.Thanx & Regards,SSG
I have a table in sql 2005 express, this table has a datetime field. I capture the date of the server like this "05/09/2006 08:17:23 a.m." in a windows form. When I try to insert this date in the datetime field of my data base, the builder show an error like there´s no possible to convert string to date time.
The insert statement is like this:
Insert into table1 (cod, date1) values (1,'05/09/2006 08:17:23 a.m')
I have a succesful insert with a date like '05/09/2006 08:17:23' without the a.m. But when I capture the date now the format is with a.m.. how can I do that?
hi guys im having real problems and dont know how to solve it at all i have a web app which allows users to enter information through edit boxes when they submit the imformation it gets added into my SQL database. does anyone know how to get the Date and Time when they insert the information and store in another column of type datetime in SQL database the web app is written in C# hope someone can help thanks
Hello The date format in SQL Server 2000 is dd-MM-yyyy. I am writing the following code in the buttons click event. I am using a textbox and button. Dim conn as SqlConnection=new SqlConnection(connection string) Dim ins as string="insert into Sample(dval) values(' " & TextBox1.Text & " ')" Dim cmd as SQlCommand=new SqlCommand(sel,conn) conn.open() cmd.ExecuteNonQuerry() conn.close() When i am inserting the date 05-03-2007 in the textbox and clicking the button it is inserting date 03-05-2007 rather than 05-03-2007. What changes should i make in the code? Rathish
I am using VB6 and SQL7. I am asking a user to enter a date if applicable - specifically a product manufactured date. I then take that date, using the dateadd function, query a table for a specific code, and calculate an expiration date based on the particular code.
The problem - if the user does not enter a manufactured date, which is OK, SQL inserts 01/01/1900 - which I do not want. How do I handle?
I am inserting the date, if there is one, from a flexgrid. I am declaring the variable as variant. The SQL field is DateTime.
Hi again, I am having troubles inserting a datetime value in a table to a string. what iw ant to do is have it be sent in an email. its an attendance email. here is the code i have right now:
INSERT INTO [GPO].dbo.tblMetric  (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) SELECT           1 AS KPI_OWNER_ID     , 23 AS METRIC_ID     , .75 AS GOAL     , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH     , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
I need to insert some values into a table and after that catch the ID inserted. I set some input parameters in stored procedure and only one to get the @id defined as output SqlParameter paramIdPedido = new SqlParameter("@APP_IDPEDIDO", SqlDbType.Int, 4);paramIdPedido.Direction = ParameterDirection.Output;cmd.Parameters.Add(paramIdPedido); Do I have to run cmd.ExecuteNonQuery(); to record first what I need and afterrun ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID) From stored procedure: INSERT table(fields) VALUES(vars)SELECT TOP 1 @id = id FROM table ORDER BY id DESC I must do all these steps or maybe is there anything less complex to do? Thanks!
Hi Andrea, I have made a table which contain data inserted manually and also data that was inserted by using bulk insertion. I have no problems using the table with Grid View. But when I try to use a query like the following:
SELECT * FROM dbo.last WHERE VMake = 'Honda' AND VType = 'sedan' AND VColor = 'Red';
I would only get the data that were inserted manually. When I use the same query to filter data from a table that the data was inserted by using bulk insert, I get column names but no data.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Hi Guys. I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" /> <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" /> I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol... any help would be great :). Mike.
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer. That's my problem: if the server is in english version, I have to insert date with this code:
DateTime.Today.ToString("MM/dd/yyyy")
instead if the server is in italian version, I have to insert date with this code:
DateTime.Today.ToString("dd/MM/yyyy")
Is there a way to insert a date in standard way, without knowing the server version?
m inserting some data in big-sized field and small-sized fields, data of varchar type, int's, dateTime, and others... i am using something called a stored procedure to add data into a table.. now when i execute the stored procedure my data gets truncated (although i made the sizes for my fields ridiculously big like 1000 just in case) for example if i want to enter Jasmine it only inserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I am guessing it's a problem of using single vs. double quotes and stuff like that within my insert statement... the following is my stored procedure:
CREATE procedure addLabor @lName varchar, @fName varchar, @mName varchar, @title varchar, @craft varchar, @lastFour varchar, @SSN varchar, @dateOfHire varchar, @currentProj varchar, @status tinyInt, @project_id int, @updateBy varchar, @updateDate varchar, @address varchar, @email varchar, @phone varchar, @zip varchar, @myfeedBack varchar, @ethnicity varchar, @userID int as BEGIN SET NOCOUNT OFF DECLARE @newid INT insert into laborPersonal ( lName, fName, mName, title, craft, lastFour, SSN, dateOfHire, currentProj, status, project_id, updateBy,
When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName my exec statement is this: EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman', '1234', 'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==', '6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005 2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345', '123-12-1234', 'African American', '3'
Hi there I have a book and I'm learning now, but I really want to get this working as soon as possible as it will convince the boss to give me more time to study as it will blow him away. (I started this 2 hours ago and it's all working except for this bit). He's used to me building apps in weeks not hours. I have an online application form that inserts data to a sql 2k5 db and then displays it in another administrators web form. I have used login views which work great with Active Directory (Tokens) to display correct information. (It's not a highly secure app so relax). All the simple stuff works a treat, but I am trying to write the Domain/Username to the database with an update command. I can display this information on the form using Page.User.Identity.Name, but how can I add it to the update command to insert it to the DB? Here is the code for the entire page, and code behind at bottom. I want the Domain/Username to be inserted into the @ByUSername field. Many thanks =======aspx code==================== <%@ Page Language="VB" Debug="true" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>NURF</title> </head> <body bgcolor="#f3f5fa"> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="SqlWinUserRequests" runat="server" ConnectionString="<%$ ConnectionStrings:IM&TSystems on BOHWEB1 %>" InsertCommand="INSERT INTO WinUsers (ByFullName, ByPosition, ByInternalTel, ByEmail, NewTitle, NewForename, NewSurname, NewPositionHeld, NewLocation, AccDateOfRequest, AccDateRequired, AccAccountToCopy, NewInternalTel, ByUsername) VALUES (@ByFullName,@ByPosition,@ByInternalTel,@ByEmail,@NewTitle,@NewForename,@NewSurname,@NewPositionHeld,@NewLocation, { fn NOW() },@AccDateRequired,@AccAccountToCopy,@NewInternalTel, <%Page.USER.IDENTITY.Name%>)" ProviderName="<%$ ConnectionStrings:IM&TSystems on BOHWEB1.ProviderName %>" SelectCommand="SELECT ID, ByFullName, ByPosition, ByInternalTel, ByEmail, ByUsername, NewTitle, NewForename, NewSurname, NewPositionHeld, NewLocation, AccDateOfRequest, AccDateRequired, AccAccountToCopy, NewInternalTel FROM WinUsers"> <InsertParameters> <asp:Parameter Name="ByFullName" /> <asp:Parameter Name="ByPosition" /> <asp:Parameter Name="ByInternalTel" /> <asp:Parameter Name="ByEmail" /> <asp:Parameter Name="NewTitle" /> <asp:Parameter Name="NewForename" /> <asp:Parameter Name="NewSurname" /> <asp:Parameter Name="NewPositionHeld" /> <asp:Parameter Name="NewLocation" /> <asp:Parameter Name="AccDateRequired" Type="DateTime" /> <asp:Parameter Name="AccAccountToCopy" /> <asp:Parameter Name="NewInternalTel" /> </InsertParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlLocations" runat="server" ConnectionString="<%$ ConnectionStrings:IM&TSystems on BOHWEB1 %>" SelectCommand="SELECT [Location] FROM [Locations] ORDER BY [Location]"></asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlWinUserRequests"> <EditItemTemplate> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Font-Bold="True" Font-Names="Verdana" Font-Size="14pt" ForeColor="#000000" Text="Request a new windows user account"></asp:LinkButton> </EditItemTemplate> <EmptyDataTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New" Font-Bold="True" Font-Names="Verdana" Font-Size="14pt" ForeColor="#000000" Text="Request a new windows user account"></asp:LinkButton> </EmptyDataTemplate> <InsertItemTemplate> <span style="font-size: 14pt; font-family: Verdana"><strong>Request a new windows user account</strong></span><br />
=================end aspx code============= =================code behind============== Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) FormView1.InsertItem(True) End Sub Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted Dim Message As String Message = ", your request has been recieved by IM&T." System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">" & vbCrLf) System.Web.HttpContext.Current.Response.Write("alert (""" & Page.User.Identity.Name & Message & """)" & vbCrLf) System.Web.HttpContext.Current.Response.Write("</SCRIPT>") End Sub End Class
I have a primary key named pk, name and surname fields. I need to insert to my table names and surnames.
INSERT INTO People (name,surname) VALUES ('john','black');
I'm not giving pks database gives is auto. But my problem is i need to know the pk that my database gave. Because i have lots of duplicate records. Is there any way to retrieve pk while inserting to table.
Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)
need help help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008' i explain i have stord prosege that create mod cycle shift pattern and it working ok now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1 so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008
tnx
Code Block DECLARE @shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL) declare @I int set @i=0 while @i < 5 BEGIN INSERT INTO @shifts_pattern ([patternShiftValue] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 set @i=@i+1 end declare @empList TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO @empList ([empID], [ShiftType],[StartDate],[EndDate]) SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103) -- create shifts table declare @empShifts TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) DECLARE @StartDate datetime DECLARE @EndDate datetime Declare @current datetime DEclare @last_shift_id int Declare @input_empID int ----------------- open list table for emp with curser DECLARE List_of_emp CURSOR FOR SELECT emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp OPEN List_of_emp FETCH List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate SET @current = @StartDate ----------------- -- loop on all emp in the list while @@Fetch_Status = 0 begin -- loop to insert info of emp shifts while @current<=@EndDate begin INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate]) select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate from @shifts_pattern as shift where PatternId=@last_shift_id+1 -- if it is Friday and we are on one of the first shift we don't move to next shift type . if (DATENAME(dw ,@current) = 'Friday' ) and EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) -- do nothing --set @last_shift_id=@last_shift_id print ('friday first shift') ELSE set @last_shift_id=@last_shift_id+ 1 set @current=DATEADD( d,1, @current) end FETCH List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate -- init of start date for the next emp set @current = @StartDate end CLOSE List_of_emp DEALLOCATE List_of_emp select empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift RETURN
