Inserting Just Times

Jan 20, 2008

When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency.

If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM')

It winds up going in as  '1/1/1900 6:00 PM'.  This is okay.  This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question).

If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'.

Also, if I insert it using a SQL Parameter, such as...

da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime")

...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'. 

Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)? 

Finally, what is the base day for SQL Server 2005?  Is it 1/1/1900 or 12/30/1899?  I think I've seen both. 

Thanks!

Aaron

View 3 Replies


ADVERTISEMENT

DTS Buffer Times Out When Inserting To Sql Destination

Jun 6, 2007

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.



Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.



Is there a way that i can ignore the sql server destination when there are no rows to be inserted.



Thanks



[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

View 5 Replies View Related

Times

Feb 12, 2008

I have set up an sql server which has a column called times. This column is to hold race times however when i enter times such as 22:20:89 with the 89 being milliseconds i am not bale to input this it will only allow me to put times in where milliseconds are below 60. This is a problem especially for the shorter races where half a second is a big difference.
 Thanks Mike

View 2 Replies View Related

EM Times Out

Apr 29, 2002

When trying to connect to a server on a slow connection (satelite), Enterprise Manager times out. I am able to connect with Query Analyzer. Tried to change 'remote login timeout', but that did'nt help, EM seems to timeout long before this value is reached. Any ideas ?

Brgds,
Odd Oren

View 1 Replies View Related

Add Times

Jun 11, 2007

SQL Statement.

Adding times together.



I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.



They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?



Below is the SQL statement of getting times from my database if its any help.




SQL StatementSELECT LENGTH_OF_VISIT FROM NOV.MAIN;




Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

View 1 Replies View Related

Selecting Between Two Times

Feb 19, 2007

Dear All,
 I need to select records between two datetimes.
My Database Structure and sample values are as below
Fromdate                                                        ToDate
2007-02-20 09:00:00.000                                 2007-02-20 12:00:00.0002007-02-20 08:00:00.000                                 2007-02-20 12:00:00.0002007-02-20 06:00:00.000                                 2007-02-20 13:00:00.000
Query i used
select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) 
My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00'
Please help
Thanks
 
 

View 5 Replies View Related

SQL Transaction Times Out

Dec 20, 2007

Hello everyone,
I have an odd issue and I can't seem to figure it out.  I am using SQL Server 2005 and I am using Transactions in combination with Try/Catch blocks.  My query (executing the stored procedure) never seems to finish the query in query analyzer.  I have removed all statements that exectute or select, etc upon the database except for a  simple Select 'test' as test so I would have something in my Begin/End blocks. Below is my stored proc:
ALTER PROCEDURE [dbo].[ar_mainCheckRecon]
@tripID int,
@payment money,
@adjustment money,
@serviceType tinyint,
@checkTpId int,
@checkTlId int,
@checkNbr varchar(20)
AS
BEGINSET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN select 'test' as test
 
 
END TRY
BEGIN CATCH
RAISERROR('Error commiting transaction', 16,1)
 ROLLBACK TRAN
SELECT '0' as success
END CATCH
 
COMMIT TRANSELECT '0' as errorCode
 
END
I call upon the sproc by running this line in query analyzer:
exec ar_mainCheckRecon 433636, 0, 500, 0, 500, 0, 23
Is there something I am overlooking as to why the query keeps running and never stops? My current execution time is at 3 mins accoring to query analyzer!
Thanks,John
 

View 3 Replies View Related

Dates Without Times

Jul 27, 1998

Anyone know of an easy way to work with datetime columns ignoring the time part.

I want to "group by" the date so it is vital that the time part goes.

But I still want to work with the resulting date as a date (i.e. use date functions) so I would prefer to keep the datetime data type.

View 1 Replies View Related

Name Lookup 5 Times

Dec 17, 2004

Hi

I'm new in SQL. I have a big problem

Database: MSSQL 2000

I have two tables: (results) one with numeric results and one with names (importance_scale)

SELECT * FROM results

name i1 i2 i3 i4 i5
---- ---- ---- ---- ----
john 2 1 2 2 2
anna 0 2 2 2 2
phil 2 2 2 2 2
dave 1 0 2 2 2

SELECT * FROM importance_scale

imp_value imp_name
--------- ------------
2 very important
1 important
0 not important

i1,i2 etc. value it is a imp_value from table 'importance_scale'

I would like to make report which will show all results in this format (in example only first record):
name i1 i2 i3 i4 i5 etc.
----- --- --- --- --- ---
john very important important very important very important very important etc.

I was thinking about using CASE I don't want use cursor. What is the best way?

Thanks for your help

View 2 Replies View Related

Times In Sql Server

Aug 29, 2004

I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?

The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.

If I use varchar data type there is a ton of code to write when I want to sort the data.

Thanks!

View 3 Replies View Related

Various Times From Various Dates

Aug 29, 2006

Jeff writes "I am trying to write a query to retrieve information that was processed between two times of the day (2:00 pm to 6:00 pm) from a date range... I am perplexed; I am new to SQL and am trying not to become frustrated, but this seems to be beyond my skill level. Any guidence would be greatly appreciated!"

View 8 Replies View Related

How Many Times Does A Letter Appear In A Name

Nov 30, 2006

i have a column containing employee name with datatype varchar.
i want to find out that how many times does letter 'y' appear in employee's name.

am a beginner to sql
help appreciated

cheers

View 5 Replies View Related

Subtracting Times?

Mar 1, 2007

I'd like to take two DateTimes, subtract them and insert the result as HH:MM:SS,

I think you use datediff but I'm not clear on the datediff formatting to get back HH:MM:SS...

Thanks for your help!


--PhB

View 5 Replies View Related

Search Between Times

Apr 22, 2006

Hi I'm new to SQl..I have some monthly data which is being encoded as Varchar...Say forthe month of January 2005 it wud look like '200501'. For each of mymonth I have a coloums which gives me datetime and volume of the job.This is what I'm doingSelectMonth,Time ,Sum(Volume)[color=blue]>From Table A[/color]Where (( Month like '200501%' ) and (Time between '2005-01-0102:00:00.000' and '2005-01-31 06:00:00.000))group by month, timeorder by monthPRoblem: What I want is all the volume between 2am and 6am on every dayof the month?The above query naturaly will give me everything between first and lastof the month...Any help is appreciated...Balaji

View 2 Replies View Related

Times In SQL Server

Jun 9, 2006

I am using an MS Access front end to input data into a SQL serverdatabase. From Access I am generating an update query utilising aninline function. My problem is that when I update a time value,instead of updating the field with something like 08:00:00 it updatesit to 1/1/1900 08:00:00. Is there a way to force the update to onlyuse the hours minutes and seconds and ignore the date part?Any help would be greatly appreciated.RegardsTrevor

View 1 Replies View Related

Adding Times Together

Jul 20, 2005

Hi guys,I have a field in my DB called EventDate as a DateTime field,therefore it holds both the date and time together like this:'2004-10-14 08:42:57.000'.I need to add together all the times in this column for a particulardate range (BETWEEN).Any suggestions will be great.ThanksSunny:)

View 10 Replies View Related

Storing Race Times

Nov 20, 2007

Hi,
I am developing an app that manages race results and I need some advice on the best way to store the race times in sql server. As far as I know, the best datatype would be datetime.
With the data stored, I will need to be able to do things such as:


rank the results based on the race time

View 3 Replies View Related

Stored Procedure Times Out

Jun 18, 2008

Hello,
I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference.
Could anyone help with making the query a bit more effiecient?  SELECT DISTINCT
webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname,
webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename,
IpToCountry.COUNTRY AS CountryName
FROM webStats LEFT OUTER JOIN
IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN
searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN
browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN
os ON webStats.useragent LIKE '%' + os.agentstring + '%'

WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
ORDER BY webStats.datestamp DESCThanks for your help.
Bart

View 10 Replies View Related

SqlConnection Times Out In GetPassword

May 3, 2004

I have some code that opens a SqlConnection to a MSSQL 2000 database. The connection opens great everywhere except inside my implementation of IPasswordProvider.GetPassword(). It just hangs there, and then throws a timeout. I have tried using both trusted connections, and userid/password connection strings. I notice that within GetPassword, the authenticated user is COMPUTERNAMEASPNET, whereas in the web method that actually is getting called, the authenticated user is my personal user account. The SqlConnection opens fine in the actual web method.

Can anyone tell me what could stop a SqlConnection from opening while inside GetPassword()? I kinda need to look up passwords in the database.

View 1 Replies View Related

Exec SP Many Times, From Select?

Aug 5, 2004

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select.... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary T

View 4 Replies View Related

Scheduled Job Fails 1 Out Of 3 Times

Sep 28, 1999

I scheduled a Bulk copy operation, and have been getting some inconsistent failures.

Last night the job was set to run every 3 hours, I expect it to take about 1 hour to finish.

The second itteration of the job failed, the first and third ran fine.
Here is the error from DTS

Any suggestions? Is there a better/stable Data Provider for SQL?

... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSBulkInsertTask_1 DTSRun OnError: DTSStep_DTSBulkInsertTask_1, Error = -2147217900 (80040E14) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040... Process Exit Code 1. The step failed.

View 2 Replies View Related

SQL Query Load Times

Feb 10, 2006

I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?

Thanks

View 1 Replies View Related

How To Select One Record Many Times?

Sep 14, 2006

The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.

Any suggestion will be appreciated.

ZYT

View 4 Replies View Related

Function Running Many Times

May 28, 2008

Hello,

I see the following problem regarding a SQL function that returns a value.

We have the following query

SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')

The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.

We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.

We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).

Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.

Thanks in advance,

Lisandro.

View 3 Replies View Related

Compare Two Times In SQL Express

Jul 19, 2006

Hey, everyone. I'm new to database programming in SQL Server, and I currently have the 2005 express edition. I was wondering whether it was possible to check whether one time (h:mm AMPM) is less or greater than another (the other in this case being the GETDATE). I already made a user-defined function in order to extract the time from a datetime, since SQL Server doesn't support the time data type. The thing is that the value is returned as a char(8) in the 12-hour style HH:MM AMPM format and I'm sure that's what's screwing up the comparison function. What would I need to do to compare two times (24-hour or 12-hour, whichever is easier)?

View 7 Replies View Related

Don't Want To Insert Same Values 2 Times.

Sep 6, 2006

create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as


begin
set nocount on
declare @sql varchar(700)

-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'
-- and a.id_town = + @tableName
-- print(@sql)

exec(@sql)
set nocount off
end
---------------------------------------------------


when i run this SP 2 times, it will insert the values 2 times.
what should i do if i don't want to insert the same values 2 times?
I'm confussed, Should i use update? any input will be appreciated.

View 20 Replies View Related

Query - Between To Dates And Between To Times

Jul 24, 2001

Hi there, I'm a little bit stumped on this one.

I have a column in a table that records when the date and time of an event took place.

Table Name: Chronicle
Column Name: Created (of type DateTime)

I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)

Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?

cheers,
Hamish

Hamish Norton
hamish@liftrider.com

View 1 Replies View Related

How To Find The Difference Between 2 Times In SQL

Apr 10, 2007

Hi,

I am trying to find the difference between two times (timeIN, timeOut)and then populate a new field called "duration of stay" with the difference.

The two times are in a table called attendance and the new duration of stay field is to be populated in a table called factAttend2

Is there an easy way to do this?

Thanks

View 5 Replies View Related

Hourly Schedule Between Times

Nov 23, 2007

hi,
I have setup an hourly schedule to run every hour, but how can I choose a start and end time ? This is within subscriptions.
is this possible ?

View 3 Replies View Related

How To Tell How Many Times An SQL Executed In V7 Or V2000?

Feb 21, 2006

Using the dm_exec_query_stats in 2005, I know I can get the number ofexecutions for a particular sql_handle, but is it possible to get thenumber of execs for a SQL in version 7 or 2000? Also is it possible toget reads/writes/etc in these early versions?Thanks.

View 1 Replies View Related

Select Records Between Two Times

Jul 20, 2005

This seems so simple yet I can't figure it out.I have a table that has two important columns for this query.Column A has varcharColumn B is datetimeAll I want to do is select any record that is between 5:00 am and10:00 am regardless of what date it falls under.In my brain I seeSelect *From <table>Where B Between Like ‘%5%am' and Like ‘%10%am'This chokes and I suppose its because Between is literal and Like isvariable.I cant convert column B to just times because I still need to see thedate in the results of the query.Thanks in advance for help

View 2 Replies View Related

Application Times Out While Doing The Updates

Jul 20, 2005

here's the scenario..I am running a DTS to collect the summarized info from Oracle databaseinto SQL server. I then have a update job which updates mytransactional table from the summarized table.The update takes a very long time (~ 3 minutes)even though it hasaround 1500 rows which causes the application to timeout. I want thisjob to be done in less than a minute.Thoughts on improving performance. Is stored procedure a way to go?(I have used Isolation,row hints etc etc..nothing seems to be working)AJ

View 11 Replies View Related

Strange Processing Times

Jul 20, 2005

I have written a simple sql statement that I am running through theviews in sql manager that retrieves data from a couple of tables by daterange and a street name. I am getting the following run times forvarious date ranges.I did the above as a test because of a sudden reduction in retrievalresponse times.September data < 1 secondOctober data < 1 secondSeptember + October > 30 seconds (what could cause the jump)September 1 > October 5 < 1 secondSeptember 1 > October 6 > 30 secondsTotal records per month is less than 50Indexes just rebuiltSame results when running fron another PCResults are consistant from run to runOnly three fields returnedFast server, 4 processors, very little activity, 4 gig ramAny ideas where to look? Reboot server?Thanks.Charles*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved