I have a dbfield which holds a smallint value for the time eg. 1406 . I have to add this value to a datetime value in another field but need the result as a datetime value.
The result would be time(int) + date(datetime) = datetime(datetime).
I need this to compare the hours between delivery and dispatch.
A SQL Server 2005 db has three date related-columns (MonthGiven, DayGiven, YearGiven) each as smallint datatype. I would like to create a DocDate column (datetime datatype) that combines the data from the three existing date-related columns. I have tried casting and simple concatentation without success. ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime) I think I need to be doing a Conversion instead of casting but have been unable to implement info I have found in the SQL Server Developer Center in my situation.
In vb.net what kind of data can I inject in a smallInt lenght 2 Data Type? Right now I am trying to pass an integer to my SP and it gives me the error "Error converting data type nvarchar to smallint" So I guess that I am not passing the right type of data or I have to cast it? Thanks
For a SQL statement in an Alias column I am am combing severalcolumns.But I am having problems with one column as it is a smallint.I get this errorSyntax error converting the nvarchar value to a column of data typesmallintMy Sql statement "Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColNamefrom Table1I was wondering is there anyway to format/convert the smallint tonvarchar, without changing the database.
Newbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo has numbers that represent persons. SequenceNo has numbers that represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which returns a alias ('No Column Named') result value of 8 which is an arithmetic result instead of a string concatination that I want.So my questions are:1. Should the original database designer have used string data types for these columns since they will never be used for math purposes?2. Do I need to cast them to string data type (like nchar(4) - since neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?
Is an index based on a smallint (16 bit) really faster than an index based on an int (32 bit) If so, how much...
Four tables
Table A: ID smallint (PK) Text varchar(50)
Table B:ForeginID smallint (indexed - non unique) Text varchar(50) rowID int (PK)
Table C: ID int (PK) Text varchar(50)
Table D:ForeginID int (indexed - non unique) Text varchar(50) rowID int (PK)
Table A and C contain identical data Table B and D contain identical data (Tables A and B were filled and then copied to Tables C and D)
Tables A/C are loaded with 64,000 records (-32,000 to 32,000) Tables B/D are loaded with 6,400,000 records ForeginID loaded randomly with values between -32,000 and 32,000
The purpose of this test is to find out if identical queries joined on a smallint are actually faster than int based.
I ran 3 queries on each set: - Full select - Select on ID/Foregin ID - Select on Table2 RowID joined to table 1
Here are the queries:
#1. Full select (smallint) - grouped to limit result set ----------------------------------------------------------- SELECT intAID, COUNT(intBID) FROM TESTintA INNER JOIN TESTintB ON intAID = intBID GROUP BY intAID ORDER BY COUNT(intBID) desc
#2. Select on ID/Foregin ID (smallint) ------------------------------------------ SELECT intAID, intBID, strATXT, strBTXT FROM TESTintA INNER JOIN TESTintB ON intAID = intBID WHERE intAID = 29120
#3. Select on Table2 RowID joined to table 1 (smallint) ------------------------------------------ SELECT intAID, intBID, strATXT, strBTXT FROM TESTintA INNER JOIN TESTintB ON intAID = intBID WHERE intPK = 1050
#4. Full select (int) - grouped to limit result set ------------------------------------------ SELECT lngCID, COUNT(lngDID) FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID GROUP BY lngCID ORDER BY COUNT(lngDID) desc
#5. Select on ID/Foregin ID (int) ------------------------------------------ SELECT lngCID, lngDID, strTXTC, strTXTD FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID WHERE lngCID = 29120
#6. Select on Table2 RowID joined to table 1 (int) ------------------------------------------ SELECT lngCID, lngDID, strTXTC, strTXTD FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID WHERE intPK = 1050
Here are the results: (run multiple times to verify)
#1. Full select (smallint) - grouped to limit result set ----------------------------------------------------------- (8 seconds) - before computing statistics on table (13 seconds) - after computing statistics on table
#2. Select on ID/Foregin ID (smallint) ------------------------------------------ (0 seconds)
#3. Select on Table2 RowID joined to table 1 (smallint) ------------------------------------------ (0 seconds)
#4. Full select (int) - grouped to limit result set ------------------------------------------ (8 seconds) - before computing statistics on table (7 seconds) - after computing statistics on table
#5. Select on ID/Foregin ID (int) ------------------------------------------ (0 seconds)
#6. Select on Table2 RowID joined to table 1 (int) ------------------------------------------ (0 seconds)
Conclusion: Not only is there a negligible difference in select performance, generating stats on the smallint actually makes it slower. (perhaps there is some kind of conversion going on here behind the scenes?)
I modified it to accept NULL values and conform more closely to INT specification. Here is my modified function:
CREATE FUNCTION [dbo].[udfIsValidINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @ShiftByOne INT; IF LEFT(@Number, 1) = '-' SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1; SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0) IF (PATINDEX('%[^0-9-]%', @Number) = 0 AND CHARINDEX('-', @Number) <= 1 AND @Number NOT IN ('.', '-', '+', '^') AND LEN(@Number)>0 AND LEN(@Number)<11 AND @Number NOT LIKE '%-%') SELECT @Ret = CASE WHEN CONVERT(BIGINT,@Number) - @ShiftByOne <= 2147483647 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidINT('2147483648') SELECT dbo.udfIsValidINT('2147483647') SELECT dbo.udfIsValidINT('-200') SELECT dbo.udfIsValidINT('-2147483649') SELECT dbo.udfIsValidINT('32900') SELECT dbo.udfIsValidINT('1.79E+308') GO
I also have a separate function for SMALLINT:
CREATE FUNCTION [dbo].[udfIsValidSMALLINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @ShiftByOne INT; IF LEFT(@Number, 1) = '-' SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1; SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0) IF (PATINDEX('%[^0-9-]%', @Number) = 0 AND CHARINDEX('-', @Number) <= 1 AND @Number NOT IN ('.', '-', '+', '^') AND LEN(@Number)>0 AND LEN(@Number)<6 AND @Number NOT LIKE '%-%') SELECT @Ret = CASE WHEN CONVERT(INT,@Number) - @ShiftByOne <= 32677 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidSMALLINT('589') SELECT dbo.udfIsValidSMALLINT('-200') SELECT dbo.udfIsValidSMALLINT('-32900') SELECT dbo.udfIsValidSMALLINT('32900') SELECT dbo.udfIsValidSMALLINT('1.79E+308')
and one for TINYINT:
CREATE FUNCTION [dbo].[udfIsValidTINYINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @L TINYINT; SET @L = LEN(@Number); SET @Number = COALESCE(@Number,'0'); IF (PATINDEX('%[^0-9]%', @Number) = 0 AND @L>0 AND @L<4) SELECT @Ret = CASE WHEN CONVERT(SMALLINT,@Number) < 256 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidTINYINT('256') SELECT dbo.udfIsValidTINYINT('-1') SELECT dbo.udfIsValidTINYINT('0') SELECT dbo.udfIsValidTINYINT('255') SELECT dbo.udfIsValidTINYINT('1.79E+308')
And, finally, a separate function for DECIMAL validation:
CREATE FUNCTION [dbo].[udfIsValidDECIMAL] ( @Number VARCHAR(100), @Scale TINYINT, @Precision TINYINT ) RETURNS BIT BEGIN DECLARE @Ret BIT, @L TINYINT, @DSI TINYINT; SET @Number = COALESCE(@Number,'0'); IF LEFT(@Number, 1) = '-' SELECT@Number = SUBSTRING(@Number, 2, LEN(@Number)); SET @L = LEN(@Number); SET @DSI = @L - LEN(REPLACE(@Number,'.','')) IF( PATINDEX('%[^0-9.]%', @Number) = 0 ANDCHARINDEX('-', @Number) = 0 AND@DSI <= 1 AND@L>0 AND@L<=@Scale+@DSI+ CASE @DSI WHEN 1 THEN @L-CHARINDEX('.', @Number) ELSE 0 END AND @Scale - @Precision >= CASE @DSI WHEN 1 THEN CHARINDEX('.', @Number) - 1 ELSE @L END ) SELECT @Ret = 1 ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidDECIMAL('256',2,0) SELECT dbo.udfIsValidDECIMAL('-1',1,0) SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,17) SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,16) SELECT dbo.udfIsValidDECIMAL('-255.0000000000000001',3,0) SELECT dbo.udfIsValidDECIMAL('1.79E+308',9,2)
Node that the DECIMAL validation function specifically tests whether the input number can legally convert to a given decimal scale and precision. Converting a value of 0.234234 over to DECIMAL(1,0) will work, but SQL will truncate the actual decimals to fit it in that space. However, it will throw an error if you have too many whole digits.
On the whole, I was rather rushed to get these created, so there may be some errors I didn't notice. I'm interested in any improvements you guys can make to improve performance or make them cleaner.
I need to insert rows into a table which contains a smallint field for time. The times are stored in that colum as integers (898, 11345, 1259, etc.) How can I enter a time like 9:15 AM into this field? I know how to display integer data in hh:mm format but I'm stumped on how I can do the reverse.
Sql Server has many data types. For Example: smallint Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes. I want to know that If it contains like 0 or 100 or 1000 or -200 or -2000 or more or less. What will its actual size? 2 bytes or change with the value. Please also mention the reference with your answer. if available.
I need to change the datatype of a very large table from smallint to int... What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..
UPDATE P   SET  P.IsError=1 ,P.IsDrawingRevNo=1 ,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)' FROM ZPTSMGR.ProjectDrawingRaw P WHERE  P.LogId=@LogId AND  P.ProjectId=@ProjectId AND  P.Revision > 32767  (P.Revision  NOT BETWEEN  -32768 AND 32767)  --SMALLINT RANGE  -32768 to 32767.
--DataType Range --tinyint DataType  (MinVal: 0, MaxVal: 255). Its storage size is 1 byte. --smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes. --int DataType  -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes. --Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value
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