How Can I Find Values Outside Of The Smalldatetime Range?
Mar 25, 2008
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.
I have tried to find the values which are out of the smalldatetime range, with the following query
SELECT *
FROM midmar
WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'
Which doesn't quite work, and gives me values that are actually between those two values listed.
I have also tried having the WHERE clause read:
WHERE bday <'06/06/2079' AND
bday>'01/01/1900'
and that doesn't really work either.
What's going on? Is there likely another problem besides the structuring of my queries?
View 9 Replies
ADVERTISEMENT
Mar 30, 2007
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
View 1 Replies
View Related
Oct 30, 2007
i get this error when running this sp: i think i should use convert method but how?
Msg 296, Level 16, State 3, Procedure CmmSPNet_userAlbumPhotos, Line 7
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CmmSPNet_userAlbumPhotos' AND type = 'P')DROP PROCEDURE CmmSPNet_userAlbumPhotos
GOCREATE PROCEDURE [CmmSPNet_userAlbumPhotos]
@albumID int,
@orderBy varchar(100),
@orderDirection varchar(4)AS
SELECT ID,UserID,Photo,ALbumID,Title,views,date_added from cmmNet_userAlbumPic where albumID=@albumID order by
case @orderBywhen 'date_added' then case @orderDirection
when 'desc' then '31-dec-10' - date_added
/* highest date value */else date_added
endwhen 'views' then case @orderDirection
when 'desc' then 21474 - [views] /* highest integer value */else [views]
end
else date_added
end
GO
View 1 Replies
View Related
Sep 3, 2007
Hi, I'm making a webapplication in C# with MSSQL as database.
I've created a form to do an advanced search on books. The user can type in name, author, .... and he can also mark 2 dates from to Calendar objects (I made sure date one can not be bigger than date 2). I'm using smalldatetime as DBtype. The 2 selected values are DateTime in asp. The results are shown in a gridview. Since I added the feature I keep getting the same error and I can't find where it is. Here's some code:
1 public List GetBooks2(string invB,string titelB, string auteursB, string taalB, string uitgeverijB, string jaarB, string keywordsB, string categorieB, string standplaatsB, string ISBN,DateTime datum1, DateTime datum2, string sortExpression)
2 { //this is my method for the advanced search
3 using (SqlConnection oConn = new SqlConnection(_connectionString))
4 {
5 string strSql = "select * FROM Boek where 1 = 1";
6 if (!String.IsNullOrEmpty(invB)) strSql += " and (inventaris_nr like @invB)";
7 if (!String.IsNullOrEmpty(titelB)) strSql += " and (titel like @titelB)";
8 if (!String.IsNullOrEmpty(auteursB)) strSql += " and (auteurs like @auteursB)";
9 if (!String.IsNullOrEmpty(uitgeverijB)) strSql += " and (uitgeverij like @uitgeverijB)";
10 if (!String.IsNullOrEmpty(ISBN)) strSql += " and (ISBN10 like @ISBN or ISBN13 like @ISBN)";
11 if (!String.IsNullOrEmpty(standplaatsB)) strSql += " and (standplaats like @standplaatsB)";
12 if (!String.IsNullOrEmpty(jaarB)) strSql += " and (jaartal like @jaarB)";
13 if (!String.IsNullOrEmpty(keywordsB)) strSql += " and (keywords like @keywordsB)";
14 if (!String.IsNullOrEmpty(taalB))
15 if (taalB == "Andere")
16 strSql += " and (taal NOT IN ('nederlands', 'frans', 'engels', 'spaans', 'italiaans', 'duits'))";
17 if (taalB == "--Geen voorkeur--")
18 strSql += "";
19 else
20 strSql += " and (taal like @taalB)";
21
22 if (!String.IsNullOrEmpty(categorieB))
23 if (categorieB == "--Selecteer een categorie--")
24 strSql += "";
25 else
26 strSql += " and (categorie like @categorieB)";
27
28 if (datum1 == null)
29 strSql += "";
30 else
31 if (datum2 != null)
32 {
33
34 strSql+=" and datumB between @datum1 and @datum2";
35 }
36 else strSql+="";
37 if (!String.IsNullOrEmpty(sortBLOCKED EXPRESSION
38 strSql += " order by " + sortExpression;
39 else
40 strSql += " order by id";
41
42 SqlCommand oCmd = new SqlCommand(strSql, oConn);
43 oCmd.Parameters.Add(new SqlParameter("@invB", "%" + invB + "%"));
44 oCmd.Parameters.Add(new SqlParameter("@titelB", "%" + titelB + "%"));
45 oCmd.Parameters.Add(new SqlParameter("@auteursB", "%" + auteursB + "%"));
46 oCmd.Parameters.Add(new SqlParameter("@taalB", "%" + taalB + "%"));
47 oCmd.Parameters.Add(new SqlParameter("@uitgeverijB", "%" + uitgeverijB + "%"));
48 oCmd.Parameters.Add(new SqlParameter("@jaarB", "%" + jaarB + "%"));
49 oCmd.Parameters.Add(new SqlParameter("@keywordsB", "%" + keywordsB + "%"));
50 oCmd.Parameters.Add(new SqlParameter("@categorieB", categorieB ));
51 oCmd.Parameters.Add(new SqlParameter("@standplaatsB", "%" + standplaatsB + "%"));
52 oCmd.Parameters.Add(new SqlParameter("@ISBN", "%" + ISBN + "%"));
53 oCmd.Parameters.Add(new SqlParameter("@datum1", "%" + datum1 + "%"));
54 oCmd.Parameters.Add(new SqlParameter("@datum2", "%" + datum2 + "%"));
55
56
57 oConn.Open();
58 SqlDataReader oReader = oCmd.ExecuteReader();
59 List boeken = GetBoekCollectionFromReader(oReader);
60 oReader.Close();
61 return boeken;
62 }
63 }
I think that that method is correct, not sure though... The code for GetBoekCollectionFromReader(oReader) is this=1 protected List GetBoekCollectionFromReader(IDataReader oReader)
2 {
3 List boeken= new List();
4 while (oReader.Read()) //THIS IS WHERE THE ERROR APPEARS
5 {
6 boeken.Add(GetBoekFromReader(oReader));
7
8 }
9 return boeken;
10 }
That's the method where the error appears... Where should I place a breakpoint to get the exact location? To make sure all methods in this code are explained, here's the code for GetBoekFromReader(oReader))= 1 protected Boek GetBoekFromReader(IDataRecord oReader)
2 {
3 Boek boek = new Boek();
4 boek.idB= (int)oReader["id"];//id auto generated dus verplicht
5 if(oReader["inventaris_nr"] != DBNull.Value)
6 boek.Inventaris_nrB = (string)oReader["inventaris_nr"];
7 if (oReader["auteurs"] != DBNull.Value)
8 boek.AuteursB = (string)oReader["auteurs"];
9 boek.TitelB = (string)oReader["titel"];//titel verplicht
10 if (oReader["taal"] != DBNull.Value)
11 boek.TaalB = (string)oReader["taal"];
12 if (oReader["uitgeverij"] != DBNull.Value)
13 boek.UitgeverijB = (string)oReader["uitgeverij"];
14 if (oReader["aantal_p"] != DBNull.Value)
15 boek.Aantal_pB = (string)oReader["aantal_p"];
16 if (oReader["jaartal"] != DBNull.Value)
17 boek.JaartalB = (int)oReader["jaartal"];
18 if (oReader["keywords"] != DBNull.Value)
19 boek.KeywordsB = (string)oReader["keywords"];
20 if (oReader["categorie"] != DBNull.Value)
21 boek.CategorieB = (string)oReader["categorie"];
22 if (oReader["standplaats"] != DBNull.Value)
23 boek.StandplaatsB = (string)oReader["standplaats"];
24 if (oReader["ISBN13"] != DBNull.Value)
25 boek.ISBN13 = (string)oReader["ISBN13"];
26 if (oReader["ISBN10"] != DBNull.Value)
27 boek.ISBN10 = (string)oReader["ISBN10"];
28 if (oReader["URL"] != DBNull.Value)
29 boek.UrlB = (string)oReader["URL"];
30 if (oReader["username"] != DBNull.Value)
31 boek.UsernameB = (string)oReader["username"];
32 if (oReader["passwoord"] != DBNull.Value)
33 boek.PasswoordB = (string)oReader["passwoord"];
34 if (oReader["datumB"] != DBNull.Value)
35 boek.DatumBoek = (DateTime)oReader["datumB"];
36 if (oReader["status"] != DBNull.Value)
37 boek.StatusB = (string)oReader["status"];
38
39
40 return boek;
41 }
Conversion failed when converting datetime from character string.
That's the error I get by the way. It also appears when I do a search and I don't use the date function... for example when I only fill in the title textbox and I don't select any dates from the calendars...
Sorry for the long post, but I think it's the only way to get a clear view on it...
View 2 Replies
View Related
Feb 24, 2004
Hi,
I am having a column subdate as smalldatetime.
The field can have null values. But I am not able to insert null values into the database.
i tried using subdate="". But it inserts some min value of my calendar popup.
Is there a way that i could insert null values into smalldatetime field of the SQl database.
Thanks,
-sriram
View 9 Replies
View Related
Mar 24, 2008
I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ...
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
---Jim
View 3 Replies
View Related
Feb 14, 2004
hi
how can we insert null into a small datetime field (from client application VB)
View 2 Replies
View Related
Jun 4, 2007
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
View 5 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Jun 15, 2006
From a text file I'm importing date values by a variable.
dFecha = Left(Row.Column21, 4) & "/" & Mid(Row.Column21, 5, 2) & "/" & Right(Row.Column21, 2)
After that I've got values as "0000-00-00" and obviously sql (at target smalldatetime) doesn't support them. How do I for to solve?
If IsDate(dFecha) Then
.Parameters("@FecEnajenacion").Value = dFecha
Else
.Parameters("@FecEnajenacion").Value = vbNull
End If
TIA
View 1 Replies
View Related
Dec 2, 2006
Hi there:
This one's a bit tricky. I want to be able to take an IP address of a request on my website and find it in a table. Specifically, I need to be able to record IP address RANGES for search engines so that when they attempt to find a page that isn't there, I can programmatically send a 404 header instead of give them the human-friendly page (please don't make suggestions on this - I'm using PHP, etc., and can't send both a 404 header and a human-friendly page).
Anyway. Let's say a search engine has the IP range 33.33.0.0 through 33.33.255.255 (I made that up). if I receive a request from IP 33.33.101.221, how can I store ONE record (as either a regular expression or maybe multiple fields or somehow else) so that I can match that IP? I'm normally pretty good at figuring stuff like this out, but this one has me stumped for the moment.
All help is greatly appreciated.
I'll be posting a similar request on the PHP forum - I hope that's not considered cross-posting.
Jay
View 1 Replies
View Related
Mar 16, 2004
I have a database were a client can have many addresses. Clients may be at one address or another at different time of the year. The table allows for entering a startdate and an enddate for each address. I'm trying to figure out the best way to filter on this to return only the current address. I have tried the Where clause below but I'm not sure this is what i should use. The year is not needed but the datatype is datetime. I think i need to use the startdate also, but I cannot seam to get how to filter this. If anyone has ideas I would like to hear them.
Thank you,
A.Selected=1 AND A.EndDate Is Null OR DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())
View 9 Replies
View Related
Jan 17, 2008
hi all
how to find the continuous date from the given date range in sqlserver 2005
e.g.
2007-01-27 and 2007-02-02 and output should be
2007-01-27 2007-01-282007-01-292007-01-302007-01-312007-02-012007-02-02any suggestion?
View 1 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Jul 30, 2014
We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8))
INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99')
DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
[code]....
View 1 Replies
View Related
Jul 26, 2007
hi!
i have one datatable with date and some more fields are there in that. i want to query to return values from a particular date to another date. how can i do this? please help me!
thanks in advance!
View 1 Replies
View Related
Apr 13, 2015
I have a query question.
Consider a table with the following structure:
RecordID (PK - int) - RecordDate (DateTime)
I need to find all records that fall within a 7 day period slot based on the first RecordDate of a specific slot.
Example, consider the following records:
RecordID - RecordDate
1 - 2015-04-01 14:00
2 - 2015-04-03 15:00
3 - 2015-04-03 16:05
4 - 2015-04-03 19:23
5 - 2015-04-06 09:15
6 - 2015-04-06 11:30
7 - 2015-04-07 12:00
8 - 2015-04-09 15:15
The result of the query I'd like should look something like this
1
2
5
7
8
So basically I'd like to leave record 3 and 4 out because they fall within 24 hours of record 2 and I'd like to leave record 6 out because it falls within 24 hours of record 5.I'd tried working with a CTE and set a dateadd(d, 1, recorddate), join it on itself and use a between From / To filter on the join but that didn't work. I don't think NTILE will work with this?
View 9 Replies
View Related
May 12, 2015
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
View 7 Replies
View Related
Mar 29, 2012
suppose you have a large table with 2 columns
create table tick
(
ID bigint identity (1,1) primary key not null
, price money not null
)
and I want to know 3 things
Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10
So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.
I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.
View 2 Replies
View Related
Mar 7, 2008
I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.
For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!
StartStop
---------
20452296
26173775
568936948
3727084237
84409178779
179013179995
180278259121
259292306409
307617366511
View 6 Replies
View Related
Nov 13, 2014
I have this query that calculates the next possible shipping day, based on 3 conditions:
- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and
[code]...
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.
DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))
View 2 Replies
View Related
Oct 24, 2007
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
View 7 Replies
View Related
Apr 26, 2015
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group
Group intensity
AA 1120
AB 1400
BA 1800
BB 2240
CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA
2 option : ABBA or ABBB or ABCA
3 option : BABB or BACA or BBCA
or beginning from the end
1.option CABB or CABA...
or beginning from the middle
1.option BBBA or BBAB
and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
View 7 Replies
View Related
May 8, 2007
Hoping someone may be able to help with a problem I'm having with SSRS parameters....
My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.
Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.
The second parameter is to appear as a drop-down of Departments to which the User has access.
The report is to be produced for the selected Department.
Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?
Any help is much appreciated.
View 3 Replies
View Related
May 21, 2007
This is the error I'm getting. I will paste my code below:
"
DeleteCommand="DELETE FROM [Friends] WHERE [FriendName] = @FriendName"
SelectCommand="SELECT * FROM [Friends] WHERE (([FriendName] = @FriendName))"
UpdateCommand="UPDATE [Friends] SET [UserName] = @UserName, [UserID] = @UserID, [IP] = @IP, [AddedOn] = @AddedOn, [FriendName] = @FriendName, [IsApproved] = @IsApproved WHERE [FriendID] = @FriendID">
Type="String" />
DataSourceID="request_source" DefaultMode="Edit" EmptyDataText="You have no pending friend requests"
GridLines="None" Height="50px" Width="125px">
ReadOnly="True" SortExpression="UserName" />
Text="Accept" />
CommandName="Delete" Text="Deny" />
Text="Edit" />
CommandName="Delete" Text="Delete" />
ReadOnly="True" SortExpression="FriendID" Visible="False" />
'>
'>
View 10 Replies
View Related
May 8, 2006
Hello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
View 1 Replies
View Related
Mar 16, 2014
I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -
Code:
declare @strBin varchar(10)
set @strBin = ''
if @strBin = ''
set @strBin = '#%'
declare @endBin varchar(10)
if @endBin = ''
set @endBin = 'z%'
My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -
Code:
where BINLOCAT.BINLABEL between @strBin and @endBin
order by BINLOCAT.BINLABEL
Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.
View 9 Replies
View Related
Dec 12, 2014
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
[Code] ....
Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000
And so on….
View 4 Replies
View Related
Aug 13, 2007
Hi!
I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value.
I tried to use such code:
...
command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null);
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
....
But it doesn't work. As a result i've got an empty result set.
Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions.
Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.
Thanx
View 1 Replies
View Related
May 20, 2015
Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code
SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType
[Code] ....
The second dataset then has the date code changed to
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The third
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The fourth
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.
SELECT
SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT
FROM
Practice.ibvSalesByJob
INNER JOIN Practice.idvJobType
[Code] ....
If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.
View 8 Replies
View Related
Mar 9, 2008
Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies
View Related
May 28, 2008
I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!
SELECT
*
FROM
table_customer T1
INNER JOIN
table_itemsBought T2
ON
T2.custID = T1.custID
WHERE
price = '100'
OR price = '200';
View 2 Replies
View Related
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
View Related
