how do i insert into a datatime field of sql server just the time that is 10:45:00 PM, I do not want the date just the timeinsert into tblTest (LoginTime) values (@_Time)will insert the 10/10/2005 10:45:00 Pm but i just want 10.:45:00 pm how do i do that.
View 1 RepliesI 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?
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.
Hi,
I have the problem in accessing data from MS-SQL server by using Dreamwearver MX 's VB.NET page. Although it works well, in the date format, it also display time. Please see the following code.
SELECT student_ID, Material_ID, Borrow_Date, Due_Date, Renew_Date, Status, include_withFROM dbo.RecordWHERE student_ID = @student_ID
To remove time, I added the following code as follows. After that, I can preview and works well without having time.
SELECT student_ID, Material_ID, convert(varchar(10),Borrow_Date,103), convert(varchar(10),Due_Date,103), convert(varchar(10),Renew_Date,103), Status, include_withFROM dbo.RecordWHERE student_ID = @student_ID
But, when I browse, I get the runtime error. When I look more details in server, the error said " Parser Error Message: The server tag is not well formed." and error in <MM:DataSet"
Please help me.
Jon
Hi! I'va tried to find but I wasn't successful... I have a column in my database which I want it to be in minutes.seconds.cents of second... Is it possible to define this data type in sql server?
Thanks for any precious reply!
Hello
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
Any help is highly appreciated!
Kind Regards
Gayatri
Hi all,
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
Any suggestions?
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
I have a table with following data
(Id, date ,time)
11 2015/8/1
12:20:00
11
2015/8/1 12:21:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
11:21:00
12 2015/8/1
18:10:20
I need the table with following record only
(Id, date ,time)
11 2015/8/1
12:20:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
18:10:20
Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.
Below the code:
Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------
Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName
ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"
'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."
CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...
CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...
CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."
ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02
RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function
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 wanted to know how we can handle Time Zones and Daylight Savings Time in SQL Server 2000 as well as 2005.
Any pointers would be helpful.
Pranav
Hi, all experts here,
Thank you very much for your kind attention.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.Â
View 3 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.
Hi,when i create a new user in a .MDF file, the hour of creation in the field "createdate" of table "membership" is exactly 2 hours less than the time of my computer. Probably something to do with different hours between Europe and UK or ...How can i set the creation hour to exact the same hour of my computer?ThanksTartuffe
View 4 Replies View RelatedThis is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
I need to store data in the format 99:59:59, <hh:mm:ss> but SQL Server will not let me do this because the datetime field is basically a 24 hour clock. If someone, for example, finished a race in 34 hours, 54 minutes, 10 seconds, how would I represent this in a SQL table other than storing the data as a VARCHAR datatype?
Thanks
Ben
I was wondering is it possible with sql to take datetime data and total the datetime data per date entered into the database, for example
DATE TOTAL HOURS
+10/22/2007 8
|_ 10/22/2007 Time In Time Out Hours
8:00 am 9:00am 1.0
9:01 am 10:01 am 1.0
10:01 am 11:31 am 1.5
etc. etc. etc
until the 8 hours have been reached,
This output will be displayed in a report on reportserver 2005.
I have been unable to come up with a solution for this problem and would like to see if any of the experts here might have an idea.
I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.
View 3 Replies View RelatedI know there is a DateTime data type in SQL server 2000.
But is there a way to only stor time value (hh:mm:ss)?
Dear Colleagues,
I was wondering if anyone has tackled this problem. We have two competitors, Sybase and SQL Server, running together. Our dilemma is to have the production server, Sybase, be able to pass transaction log entries or nightly database backups over to the SQL Server, reporting server, either via a 3rd party software, SQL statement or some other gadget but the end result is to have the information concurrent on both the production server and the reporting server. Any ideas? Thanks in advance, Daimon
I have a table (Tickdata; Contract,Price,Timecreated) which storesTimecreated as datetime. I need to request the maximum price where theTimecreated is greater than some value, in this case 9/13/2004 3:09:29 PM.How exactly do I get data from a specified time?Thanks
View 2 Replies View Related
I am moving the data from a flat file to a table in SQL Server 2005.
One of the field in the table is time stamp which tells me when the data is loaded.
Which is best way to populate this field (time stamp) with constant time stamp for the entire batch data?
Thank you,
SQL Server 2005 SP2.
When I enter data into a SQL database and save it, the data that I have just added disapears, but when I close the applicaiton and re-open it, it's still there - is there any reason for this and any way to get it to stay?
Any ideas?
Hi,
The time column type is SMALLDATETIME. Data is inserted every minute, but it isn't occured always.
When I display the contents, I want show every time(minute). If a specific time is skipped, it should be display as data zero.
Someone help?
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
Has anyone seen this error? I am getting this error on one of my server. It is an ongoing error being reported to the error log.
99/03/16 13:21:52.80 spid3 flush_cache: timeout, data-in-motion, bp 0x118b340, pg 0x3c8, stat 0x100c/0x244000, obj 0xf0d453d, bpss 0)
99/03/16 13:23:32.80 spid3 flush_cache: timeout, data-in-motion, bp 0x118b340, pg 0x3c8, stat 0x100c/0x244000, obj 0xf0d453d, bpss 0)
99/03/16 13:25:12.80 spid3 flush_cache: timeout, data-in-motion, bp 0x118b340, pg 0x3c8, stat 0x100c/0x244000, obj 0xf0d453d, bpss 0)
99/03/16 13:26:52.80 spid3 flush_cache: timeout, data-in-motion, bp 0x118b340, pg 0x3c8, stat 0x100c/0x244000, obj 0xf0d453d, bpss 0)
Right now i'm developing a time attendance system.
So in that case i'll be playing with time where that time represents what time an employee come to office and go home.
I'm a little bit confuse which data type i'll be used in this system.
Because there will be a lot of calculation according time data inside the database, such as: how many times an employee comes before 08.00, how much hours that he/she spend in the office that they, etc.
Can somebody help me with this problem, what data type most suitable with this kind of system and how can I do the calculation more easily?
thanx before,
i've got an excel spreadsheet generated everyday which stores the duration of talk time as 0:02:09
When i use the Integration Services Package to move the data into a table. I've used datetime column. It comes up as 1899-12-30 00:00:02:09.000
What's best practice to store duration as in a SQL Server table? or should i convert it to seconds.
Hi
I´m a SysAdmin and I´m implementing ASSP (anti-spam) to use with an email server.
But the ASSP requires a txt file with all domains hosted in the server that are in a SQL Table of the server.
My questions is: Is there anything to make a real time TXT file with all the domains ?
maybe use trigger I dont know..
Is it possible to compress the data at restore time?.
Backup is coming from a not compressed database.
The backup file is compressed.
I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc.
If I run 'BACKUP DATABASE xxx TO DISK = 'D:DB_Backupsxxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?
Any ideas would be much appreciated.