Hello,
I need to store the start and end time of each weekly class into a
database. so that people can easily search for classes based on time.
for example, someone might want to search all classes that are offered
between 9am and 2pm.
How can i implement this time field? i don't have a date to store with
the hour and minutes. is there and way sql server can tell 2pm is
bigger than (>) 9am?
thank you in advance,
eddy
Hi All,
Is there any Tool or System SP for comparing two different server database Store Procedure.
For Ex:
Want to have SP sinc between Stage1 and Stage 2 Server.
If I want to do it manually will take lot of time because there are around 500 SP and around 12 DB.
Anyone can help in this issue.
I have to compare two time duration for a resource booking system. for example if a resource is booked for 9.00am to 2.00am ,it must not be booked during that duration and other coinciding times e.g 1.00pm to 2.00 pm should not be booked or even 9.00 am to 5.00pm should not be booked.
how can i write a sql query for this.
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.
Column 1 is holding a true statement that we need to update to false if over 10 mins old...
how to synchronize and compare two database in sql server 2000 without using any others software in real time.
my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly.
if one database had change, the other will also change
how to synchronize and compare two database in sql server 2000 without using any others software in real time.
my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly.
if one database had change, the other will also change
This is a cross-post from the Office PPS Planning site: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3379691&SiteID=17 I was hoping there may be some additional MDX resources here.
I'm trying to determine if the currentmember of the time dimension is > or < the value of today. I want to use this to change the the value of a member that I display on columns from actual (for prior to today) to forecast (after today). I have this MDX:
with
member [measures].[PYCY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())-1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)))
member [measures].[CYNY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)))
member [measures].[NYNY2] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+2)))
member [measures].[test2] as Format(Now(), "yyyyMM")
member [measures].[test4] as [Time].[Day View].CurrentMember.Member_Key
member [measures].[diff] as DateDiff('m', Format(Now(), "yyyyMM"),[Time].[Day View].CurrentMember.Member_Key)
select
StrToSet('{' + Generate(Descendants(
{strtomember('[Time].[Day View].[year].&[' + [measures].[PYCY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[CYNY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[NYNY2] + ']') }
, [Time].[Day View].[Month]), [Time].[Day View].CurrentMember.UNIQUE_NAME, ", ") + '}')
* {[measures].[test2],[Measures].[test4],[measures].[diff]}
on 0,
[Scenario].[All Members].members
on 1
from [SalesForecasting]
Here is the result for 2 months:
October FY2007
October FY2007
October FY2007
November FY2007
November FY2007
November FY2007
test2
test4
diff
test2
test4
diff
All
200805
200610
#Error
200805
200611
#Error
None
200805
200610
#Error
200805
200611
#Error
Actual
200805
200610
#Error
200805
200611
#Error
Forecast
200805
200610
#Error
200805
200611
#Error
Override
200805
200610
#Error
200805
200611
#Error
Plan
200805
200610
#Error
200805
200611
#Error
The error is: #Error Execution of the managed stored procedure DateDiff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..
Why can't DateDiff calculate the difference between 200805 & 200610?
Thanks
I have to do a report for each hour compare yesterday each hour of sales amount their output is below, how to write a query.
desire output 08:00 am -23:00pm
shop today Time yesterday current Amt Yest Amt diff amount
001 13:00-14:00 13:00-14:00 $10000 $20000 -10000 (down)
002 14:00-15:00 14:00-15:00 $10000 $15000 500 (up)
Time as at HH:MM
Yesterday Total $20000
today Total $35000
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
If we want to compare datetime vs a specific time what should we do?
i.e.  startdatetime                     specific time
     2015-10-12 00:03:19:020               16:23:00
I want to compare just time in startdatetime coloum vs a specific time i.e. 16:23 .
I convert startdatetime to (DT_DBTIME) after that i want to calculate just time difference. how can i do it?
i.e i want to calculate this two (DT_DBTIME) columns without any date ==> (16:23:00)-(00:03:19)
Is there any posibility to store only time and not date in sql server ? Even if there is no data type to support it, is there any round about way of doing it ?
Should I choose datetime in sql?
How can I display 01:25,125 on a website?
Hi,
I want to store time into a datetime datafield in MS SQL Database, but eventually, it was include a date prefix the time ! I had try the approach on following and get the invalid result.
Approach 1:
Dim strTime as String = "11:59:59 AM"
Output:
01-01-1900 11:59:59 AM
Approach 2:
Dim dtTime as DateTime = "11:59:59 AM"
Output:
1/1/0001 11:59:59 AM <- (error occur: unable add to DB cause date is not between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM)
* The time on the above is accompany with AM/PM.
Approach 3:
Dim strTime as String = "11:59:59 AM"
Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)
Output:
01-01-1900 11:59:59 AM
Approach 4:
Dim strTime as DateTime = "11:59:59 AM"
Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)
Output:
String was not recognized as a valid DateTime.
I know what cause the approach 4 get an error. That is because the strTime is declare as DateTime and that contain "AM/PM" in time.
My Question:
1]Can I just insert the time only to the DB without date?
2]If possible, what should I delcare in the variable(String/DateTime/..?)
3]How can I deal with the "AM/PM"? It must concate with time to identify daytime/night
If you know and have the solution, pls do me a favor and will be more appreciated.
Thank you
Calvin
INSERT INTO post(Comment, A_ID, Title, Date)
VALUES ('#Comment#', '#A_ID#', '#Title#', '#Date#')
There's my query...
I was hoping there's a way to make the Date, which I have as date/time, get taken from my computer so the user doesn't have to enter anything into the text boxes on my page.
I'm using ColdFusion, and know I can do this through code, but am wondering if it's possible for sql to do this also.
And, if you know of any good ColdFusion sites, I would appreciate it...I really want to nest <cfoutput> tags, but it's really tricky.
question - need to store only TIME in the fields what to use ?
when i CREATE new TABLE and to store only TIME in the fields what to use
smalldatetime ?
smalltime ?
need to store only TIME like
06:25
17:45
and need to see only the TIME not 01-01-1900:06:25
TNX
Hi all ,
What datatype should I take to store time in a table -- datetime , float or decimal?
my requirement is to store "Worked Hours in a day by an employee" in the field say, 9 hrs and 30 mins.
I should be able to manipulate data in this field such as total hours present in the month, extra hours worked in a day (considering 9 hrs as standard time),less hours worked in a day, and so on
I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Many thanks.
I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong?
My original version that calls a store procedure
Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String
Dim mySqlCommand As New SqlCommand
Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString())
Dim myPart_id As String
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "getPartId"
mySqlCommand.Connection = mySqlConnection
mySqlCommand.Parameters.Add(New SqlParameter("@part_supplierserialnumber", part_supplierserialnumber))
Try
mySqlConnection.Open()
myPart_id = mySqlCommand.ExecuteScalar()
Catch ex As Exception
myPart_id = ""
Finally
mySqlConnection.Close()
mySqlConnection.Dispose()
End Try
Return myPart_id
End Function
My Store procedure
create procedure getPartId@part_supplierserialnumber as nvarchar(50)as
select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @part_supplierserialnumber order by part_datecreated desc
GO
The new version I tried which happen the same thing
Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String
Dim myPart_id As String
Dim strSql As String = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc"
Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString())
Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)
Try
mySqlConnection.Open()
myPart_id = mySqlCommand.ExecuteScalar()
Catch ex As Exception
myPart_id = ""
Finally
mySqlConnection.Close()
mySqlConnection.Dispose()
End Try
Return myPart_id
End Function
Hello:I run one process that calls the following the store procedure andworks fine.create PROCEDURE sp_GetHostSequenceNumASBEGINSELECT int_parameter_dbf + 1FROM system_parameter_dbtWHERE parameter_name_dbf = 'seqNum'UPDATE system_parameter_dbtSET int_parameter_dbf = int_parameter_dbf + 1WHERE parameter_name_dbf = 'seqNum'ENDGOIf I run two processes that call the above store procedure, I mightoccasionally get the dirty data of int_parameter_dbt. I guess that iscaused by two processes accessing to the same resource simultaneously.Is there any way to lock the store procedure call from MSSQL Serverand allow only one process to access it at a time?Thanks for help.Best Jin
View 2 Replies View Related
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedI am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
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
[code]...
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies View RelatedI need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?