Data Entry In A Datetime Field
Jan 22, 2007
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid.
.Net Framework Datatype: Datetime
Error Message: Index was outside the bounds of the array.
Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
View 9 Replies
ADVERTISEMENT
Jun 1, 2007
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid.
.Net Framework Datatype: Datetime
Error Message: Index was outside the bounds of the array.
Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.
---
http://www.sqlserver2005.de
---
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
View 29 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Nov 9, 2007
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
View 4 Replies
View Related
Mar 12, 2007
Finding the earliest datetime entry, and then updating the database on these reults.
I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example:
My Fields in the table are as follows.
Log_ID, Cust_Ref_No, ActionDateTime, Action_Code
The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e.
12345,ABCDEF,01/01/2007 00:00:01.000,6
12346,ABCDEF,01/01/2007 09:00:00.000,6
12347,ABCDEF,01/01/2007 17:00:00.000,2
In the above I need to change the first record Action_Code from a 6 to a 1, but leave all other records unaffected.
All help greatly appreciated, Thanks : o )
View 10 Replies
View Related
Oct 15, 1998
I would like to do something like this, but it does not work.
Select * from PS_AUDIT_EMPLYMNT
WHERE AUDIT_STAMP LIKE `Oct 15 1998%`
*Note AUDIT_STAMP is a Datetime field
Does anyone have any ideas why this will not work?
Thanks,
Rodney
View 3 Replies
View Related
Oct 2, 2006
Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:
1. String was not recognized as valid DateTime
2. Operand type class; text incompatible with DateTime
I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.
Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).
I would appreciate if someone could please give me an example that I can literally insert without error.
View 4 Replies
View Related
Jan 16, 2007
Hi everybody,
I have two problems while using a analysis service cube as data source for a reporting service report.
1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:
An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.
How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.
2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.
As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.
How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?
Thanks,
StSt
View 4 Replies
View Related
Apr 19, 2006
Hello,
I have an Excel spreadsheet that contains 100,000 + rows of data. Two of the columns in the spreadsheet are date fields that are sent over in the yyyymmdd format (no separators such as "-" or "/"). I tried converting these fields into dates using the Data Conversion task in SSIS. This doesn't work as it appears that SSIS cannot map a string in the format of yyyymmdd to a valid date. In lieu of this, I have added a Script Component to my transformation which can take the string and convert it to a date value using some Substring() calls on the source yyyymmdd string.
A few questions for the group ...
1. Is this there a more efficient or "better" way of performing this transformation?
2. I can also use OLE DB Command task in my transformation and make a call that might do something like this: select convert(datetime, convert(varchar(10), [@yyyymmddValue])) but how would this perform related to the Script Component task? My guess is that this would be slower since the transformation would make a database call for every record.
3. Are there any plans for the SQL team to make these types of conversions easier to implement in the future? It seems to me that this is a pretty common scenario for integrating date data.
Thanks!
Brian
View 19 Replies
View Related
Sep 20, 2006
I am taking my first stab at a 3 tier architecture within ASP.Net. I have been adding datasets and creating a new Insert to add certain parts to a table. I am trying to add a date field called 'DateAdded' and is setup in SQL as a DateTime. When Visual Studio auto created the dataset, the Insert function is not "DateAdded as Datetime" as I would have expected, but it is "DateAdded as System.Nullable(Of Date)". There is a space in between 'Of' and 'Date'. If I keep the space in there the insert function shows an error that says "Arguement not specified for parameter DateAdded of funtion( etc. etc.). If I take the space out, the error on the insert function goes away but there is an error within the "OfDate" that says "Array bound cannot appear in type specifiers". I am confused on why the date format changed and how I can get a date to go into the database using the autogenerated datasets from Visual Studio. Any help would be appreciated. Thanks, Mike
View 1 Replies
View Related
Nov 17, 2007
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View 4 Replies
View Related
Jul 10, 2007
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
View 7 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Nov 17, 2012
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
View 2 Replies
View Related
Dec 14, 2005
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
View 4 Replies
View Related
Jan 15, 2006
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
View 8 Replies
View Related
Apr 10, 2007
question... i heard a bit about Control Macro Generator but have not found much reading material on it. here at our company we have employees fill out a weekely timesheet and typically have clerks enter the information into the TM.PTA.00 (Project Timesheet with Rate/Amount Entry) and submit it that way. say we have this timesheet in a nice little excel sheet with columns that match the input boxes on the form, how can we get it to automatically input the data instead of having a person sit there and type it all in?
View 3 Replies
View Related
Jan 28, 2008
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
View 5 Replies
View Related
Apr 17, 2007
Hello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
View 3 Replies
View Related
Jun 1, 2000
Hi,
Do you have any idea if there is / where can I find web based data entry into SQL 7.0 database. What I'm looking for is something like Oracle Forms but for SQL 7.0.
Thanks in advance,
Boaz
View 1 Replies
View Related
Nov 23, 2007
hey folks,
i am looking for code to show the last entry of a database that has a unique field. for eg.. i have columns AREA, NAME, NUMBER. i need to show the last entry for each unique area.
so if there was 5 seperate areas, the table would show the last five entries for each area. anyone help?
View 6 Replies
View Related
Jun 19, 2008
Hello all,
I'm a newbie to SQL Server. I would like to practice/learn setting up web based data access. Using SQL Server 2005 and probably a sample database like AdventureWorks, I aim to create a web based interface that will allow for data access and data entry. I'm almost finished with a book about T-SQL, and I am starting to read the MS Implementation and Maintenance book, but where do I start to learn about creating the web interface? What technologies/languages are important? ADO.NET? VB.NET C#.NET?
Thanks in advance.
View 2 Replies
View Related
Mar 31, 2015
I have a query that pulls data by date so that I have every day of the month but I would like to group it by month so that I only have one entry per month. Can that be done?
View 2 Replies
View Related
Jan 22, 2006
Hi all,
I have been using SQL Server 2K for a couple of months now and I have been entering data into my tables through Enterprise Manager. I do this by selecting the table, right-clicking on it, and selecting Return All Rows.
After months of use, this method of entering data has become tedious and cumbersome. Is there an easier way to enter data into SQL Server? I don't know how to program Visual Basic, but I know how to create dynamic websites.
Microsoft Access offers the ability to make Forms, so that data entry is easier. Can SQL Server offer anything similar? Maybe SQL Server 2005 offers a GUI for data entry?
Cheers for your help!
View 11 Replies
View Related
Jul 22, 2007
Hi Guys,
I'm using Visual Basic 2005 Express and SQL Server 2005 Express. I have textboxes on a VB form linked to 2 database tables.
I am wondering if it is possible to use just ONE BindingNavigator to manage data entry and updates to THE database tables. I initially thought I could manage the tables but have I encountered some problems:
i)When I entered a new record, and clicked on the SAVE BUTTON the new record the textboxes for the 1st table saves the record to the database, but the textboxes for 2nd table still retained data in them and are not saving theirs to the database.
ii) The same textboxes for the 2nd table are NOT allowing for updates too! Or, could it simply be that it is not possible to use this method for data entry and updates?
Thanks you.
View 1 Replies
View Related
Nov 20, 2007
I am researching ideas for the best way to have users enter data into SQL.
I am familiar with VB and Access Forms and HTML coding but what I am looking for is a way to have a standalone app or website where users can enter information but I want the form to have branches that change depending on user input (such as selecting a meeting type then having specific questions open up related to it).
Any ideas on the best way to do this?
If a different forum would be better for this request please advise and I will move appropriately.
Respectfully,
Lewis
View 2 Replies
View Related
Oct 5, 2007
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Thanks,
View 1 Replies
View Related
Apr 16, 2008
In my search results I need to order my data by a particular entry rather than a particular column and then add all the other entries onto the end of the result set. Specifically I'm trying to order my records by a particular location in a city so those entries in that location appear first in the result set and then on the end I want to include all the other locations in that city.
I'm assuming I'll need to do these as separate queries and then use a UNION statement but I'm wondering if there is a better or easier way. Is there, for example, another type of ORDER BY clause that lets me order by a particular entry rather than a column?
Thanks
View 12 Replies
View Related
Mar 3, 2008
Hi, I'm using SQL 2005, and I want get the total of data for February as an example. My table table is like this
submitdt
-----------
01/02/2008
25/02/2008
29/02/2008
01/03/2008
02/03/2008
so total submit date for february is 3. Your idea is highly appreciated.thanks you so much
View 7 Replies
View Related
Dec 12, 2007
Has anyone here been able to incorporate a way of data entry using reporting services. What I would like to see is a single page that not only provides the report but also allows for the entry of data. It would be much cleaner to have both together.
View 7 Replies
View Related
Jan 17, 2007
hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:
experienceFirst job.textboxSecondjob.textboxThirdjob.textbox
i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ....which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @@identity to get the ID from my first table(personel)which is autogenerate number. It works fine with Firstjob table but not secondjob table.
Generated error message:
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.
The statement has been terminated.
INSERT INTO dbo.Personnel
(Name)
VALUES ('Jon')
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)
values('Teachier, 'Gam', '7thn', scope_identity())
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel)
values('Musician', 'KKSB', '8thn', scope_identity())
any suggestions....
View 2 Replies
View Related
