DATETIME AS PrimaryKey?
Hi Dears!
declare @dtpk smalldatetime
select @dtpk=getdate()
print @dtpk
in the above, i m using @dtpk as a primary key value in a
table to keep a track of some events that are occuring with
a difference of one second. the above method gives me value
to store in the Primary key Filed (EventTime smalldatetime)
in the form "Dec 1 2005 7:57PM" where my requirement is
to save it in the form "Dec 1 2005 7:57:12.03PM" so as to
make it unique for the primary key field(EventTime
smalldatetime). how to get required datetime format with
the help of above statements to store as primary key in
EventTime field of type smalldatetime.
I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key.
There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.
I want to know if this primary key behavior is unique to Access or if it is THE WAY that all industry-standard primary keys work. Thus, I hope not to get too messed up on my SQL Server 2005 project--or any other SQL project.
In Access, I set up a table 'tbl_Gen_Admin_sto' to store general administration expenses. This is a budget. So, there can be at most one entry per budget_year / cost_center / account_number / month. If there are two or more entries, the data is messed up, something is wrong.
Therefore, I defined a muliple-field primary key for tbl_Gen_Admin_sto. If I make a programming mistake and enter data multiple times, the primary key feature will stop my program from entering the same data multiple times.
As I often do, I copy the data into another table, in this case tbl_Gen_Admin_tmp for manipulation in a form. (I.e., I connect a form to tbl_Gen_Admin_tmp.) The tmp version is a pivoted version of the sto table. So, in sto, I have one amount field for each month. In tmp, I have no month field, but do have one amount field for every month of the year (12 amount fields).
The transfer of data to tmp went fine. The user adds, changes, and deletes records in tmp through the form. When the user is done and wants to save the changes (and not discard changes), my program deletes the selected records from sto, then inserts the records from tmp.
But lo! Access was not accepting the new records because (error message) "key violations". The delete went fine. But the insert would not take.
I deleted the multiple-field primary key index in sto. Then I created a multiple-field regular index with no duplicates allowed. Now, sto accepts the insert.
I thought that when I deleted the records, the primary key field is gone--zzzzpft! But aparently Access is keeping track of all primary keys. Even though I deleted the record, for all eternity I will never ever be able to re-insert with the same primary key.
Here is where I need your help: Am I going to run into this same primary key behavior in SQL Server 2005? If so, is it because I have encountered the fundamental definition of primary key?
(I hope you don't beat me up too much for abusing primary keys.)
I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...
I don't have knowledge in T-SQL like IF...ELSE my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...
What i'm doing is like this:
Use osa (Destination Database) Go
DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)
My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.
How is this possible sir? Anyone care to help? Thanks in advance! More Power!
Hey, I have a page that inserts into a customers table in the DataBase a new customer account using this function: Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "") Dim result As New DataSet Dim tempid As Integer Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn")) Dim Adcust As New SqlDataAdapter Adcust.InsertCommand = New SqlCommand Adcust.SelectCommand = New SqlCommand Adcust.InsertCommand.Connection = conn Adcust.SelectCommand.Connection = conn sessionExists(email, sessionid, 1) conn.Open() If fname = "" Then Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')" Else Dim strsql As String strsql = "Insert Into neelwafu.customers" strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values(" strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')" Adcust.InsertCommand.CommandText = strsql End If Adcust.InsertCommand.ExecuteNonQuery() Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers" tempid = CInt(Adcust.SelectCommand.ExecuteScalar()) conn.Close() Return tempid End Function -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Now, I am getting an error: Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The customers table has as a primary key the 'email'..... so plz can I know why am I getting this error ???? Thank you in advance Hiba
I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Hi, I'm inserting a datetime values into sql server 2000 from c#
SQL server table details Table nameate_test columnname datatype No int date_t DateTime
C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values?
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
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
Hi, I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date '31/08/2006 23:28:59' I get the error "...datetime data type resulted in an out-of-range datetime value" I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be? cheers, Ernest
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from ( select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a ) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -
hi, How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003 1/13/2007 01132004 1-1-2004 1.2.2001
there is no uniformity of the data that is stored currently.
I have a table that has a unique ID and a datetime of when something changed.
See example: IDÂ Â Â TimeStamp 16094Â Â Â 2013-11-25 11:46:38.357 16095Â Â Â 2013-11-25 11:46:38.430 16096Â Â Â 2013-11-25 11:46:38.713 16097Â Â Â 2013-11-25 11:46:38.717 16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
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?
Hi All,This is only my second day using C#/asp.net and English isn't my native language so I'll apologise in advance if this question doesn't make sense and/or is stupid . I'm trying to make a little event planner which was going well until I hit a small problem, the user needs to enter the event name and choose a date/time from dropdowns, this bit works okay... however I'm having difficulties with storing the date/time, at the moment I'm making a DateTime object and filling out the year/month/day/hour/minute selected on the form and adding that to the database field (which is a datetime field type).Problem is, I'm using the date format DD/MM/YYYY HH:MM:SS but the database stores it as MM/DD/YYYY HH:MM:SS so if I wanted to do things like: select * from tbl where date > DateTime.Now - it isn't going to work because the month is switched with the day in the DB-versions. Just to try and give another example, if I'm not being very clear, if I print DateTime.Now.ToString() it will display 10/06/2008 02:50:48 however if I put it into the database and pull it back out cast to a DateTime Object and then display that object via ToString() it'll be 06/10/2008 02:50:48 Any suggestions on what to do would be appreciated, usually in cases like this I'd just use the unix timestamp, but since C#/ASP.NET doesn't support that natively I was hoping there was an easier/native alternative. Also I don't have access to the server configuration so changing the localization of mssql isn't an option unfortunately.Thanks in advance!
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
Hello, I used datetime as the data type for my table. But when I display the table in my GridView I get dates in the format 12/30/2007 12:00:00 AM. What datatype should I use if I want dates of the format 12/30/2007 without the time? Thanks. I
I feel really stupid asking this but im not at home and dont have my sql book with me so here it goes. How do I use Datetime? I want i want to use the built in function to do it. So would i like do something like Date = getdatetime()?
Hi ..... I have Column (Reg_Date ) as DateTime I Have tow Problem 1- When I insert value in this field the time inserted automaticly ( I don't want insert time in this field I want use it as Date only ) 2- When I display the table in DataGrid the time appear also what should I do to display the Date only ? sorry for my english ..... thanx
This simply shouldn't take all morning to figure out but for some reason it has. I simply want to insert the current date and time into a datetime field.
No matter what I try I either get errors (Syntax error converting datetime from character string.) or I get the wrong date (4/11/1900, 1/1/1900).
Here's my current SQL which gives the syntax error.
