Transact SQL :: Get Check Time From Separate Fields
Oct 14, 2015
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it.
Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.
Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school.
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
I am importing an XLS file into one of my tables. The fields are:
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
When I do the import, I get the following:
Date Id Time IO 12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2 12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1 12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
Here are my doubts:
1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages? 2. If I don't combine them, should I use varchar or datetime data type? 2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?
Any comments or suggestions will be very much welcomed.
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
I have a table which has fields called defid, datavalue, pid etc. I have to create a new table which should have fields: CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.
COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows: Thank you in advance.
SELECT DISTINCT c.CustID, d.datavalue, f.name FROM dbo.Customers c INNER JOIN dbo.Date7 d ON d.pid = c.root INNER JOIN dbo.fielddisc f ON f.defid = d.defid WHERE c.separatedate BETWEEN @StartDate AND @EndDate AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472 ORDER BY c.root
i have two columns in a "release" table that i concatenate on my aspx page into one value for my dropdown list. for example, my database columns might look likeMajor: 3 | 3 | 4 | 4 | 5Minor: 1 | 2 | 1 | 2 | 1and my drop down list text like3.1 | 3.2 | 4.1 | 4.2 | 5.1my question comes when trying to take the drop down list's selected value and splitting it back into two fields so that i can use it for another query. how can i split my "major" and "minor" version numbers back up so that i can run a query similar to:SELECT * FROM [Version] WHERE (([Major] = @Major) AND ([Minor] = @Minor))but only having the one parameter of: <asp:ControlParameter ControlID="ddlVersion" Name="Version" PropertyName="Text" Type="String" /> where the selected value has a string of "<major>.<minor>" (where the numbers are separated by a period)?
I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?
I have a table that has some comma separated values and then have another table with the lookup values. I"m not sure why it was created this way and have not had to do this. This is a vendor database so I can't go around and changing things.
Table A IDStageIDs 188 288,86,87 388,87
Table B (Lookup table) IDName 86test1 87test2 88test3
I need to separate the date stamp (which looks like this 2006-10-05 09:08:41.000) into the date in this format 05OCT2006 and then the date stamp separate in this format 09:08. Thanks!
TransactionsImport (which is the destination table) TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport Get the records with the latest date and time in the destination table TransactionsImportDelta table Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID 2011121305154107142201008300100 2011121305154122B1L13ZY0000A07YD 2011121304504735142201090002600 2011121304504737142201095008300 2011121304504737142201090002600
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status] @Salesmen nvarchar(50), @QuoteStatus nvarchar(50) AS BEGIN SET NOCOUNT ON;
I have a flat text file with lots HTML tags and corresponding values
For example <Near_Side> 5563 </Near_Side> <Top_Down_Code> Xe345 <Top_Down_Code>
So, For example I can use the a function
ALTER function dbo.StripHTML( @text varchar(max) ) returns varchar(max) as begin declare @textXML xml declare @result varchar(max) set @textXML = REPLACE( @text, '&', '' );
[Code] ...
To which Select dbo.StripHTML('<Near_Side> 5563 </Near_Side>') Value
I'll get 5563
However how would you get the values within the tag itself ? E.g.
Value Name 5563 Near_side Xe345 Top_Down_Code
I was thinking along the Charindex but cant seem to get it right.
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:-- CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVER AFTER CREATE_DATABASE AS --execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
Hi , I have a table that holds CityName and TownName.This table is usually updated and I dont want the same CityName and TownName to be inserted Again. I have used Primary Key that holds thoose two fields but Primary Key is an index and I know that using check constraint works by spending less performance then indexes .
So , How can I use a check Constraint for two fields ?
Hi, I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView. Is it possible?
hi need help from the wonderful people here i am working on project and convert a OLD DOS program to SQL server on the web this is the resalt i need to show
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006
and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas, Thanks in advance, Aldo.
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
Hey Guys, I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table. Example User enters today's date and 5:30 AM and the search for all available techs to perform that task
the tables ddl is USE [Schedule] GO /****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AllDays]( [ID] [int] NOT NULL, [DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF USE [Schedule] GO /***Appointments Table where trouble is ***** /****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Appointments]( [ID] [int] IDENTITY(1,1) NOT NULL, [Customer_ID] [int] NOT NULL, [Tech_ID] [int] NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NOT NULL, [App_Date] [datetime] NOT NULL, [Created_By] [int] NOT NULL, [Date_Created] [datetime] NOT NULL, [Sku_ID] [int] NOT NULL, [Comment_ID] [int] NOT NULL, CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [Schedule] GO ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID]) REFERENCES [dbo].[Comments] ([ID]) GO ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID]) REFERENCES [dbo].[Techs] ([ID])
USE [Schedule] GO /****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Schedule]( [ID] [int] IDENTITY(1,1) NOT NULL, [Tech_ID] [int] NOT NULL, [AllDayID] [int] NOT NULL, [ShiftStartTime] [datetime] NOT NULL, [ShiftEndTime] [datetime] NOT NULL, CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [Schedule] GO ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID]) REFERENCES [dbo].[AllDays] ([ID])
Plus the Techs Table which holds their ID's names etc I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help
I have a Microsoft Access 2007 Database I've developed here that was designed for 2 users. Our CEO saw the Access Database and has added about 20 users. Since the additional users have been given access the Backend Access Database is being killed. I've moved the Data to SQL Server 2005 but there are 2 fields timestart and timeend that worked fine in Access but after moving them to SQL Server now show a datetime of 12/30/1899 7:00 AM instead of just 7:00 AM. So If I run a query to show the time schedule for 03/21/2008 I get nothing. How can I write the query so that it only looks at the time portion of those fields?
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
Is there a global variable or something of the sort that would tell me how long it took to execute a query??
I need to monitor my DB response times and we have a query that runs in under 2 seconds. So we want to run this query every couple of minutes and if it takes more than 12 sec to run, we want to send an email to our DB staff...
I know that I could take a time stamp before and after then subtract but I wanted to know if there was an easier way to do it..
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person select id,name,Address from person <showPlznXML... 010101 column id select id,name,Address from person <showPlznXML... 010101 Table Person
