I have a SmallDateTime data type field. The SmallDataTime contains day/month/year hour:minuteeconds AM/PM. What I wanted to do is create a store procedure that will only take the month of the field and compare that to my variable. So for example, I wanted to something like the following:
@month varchar(50) = "3"
SELECT * FROM myTable WHERE monthField = @month
Any help is much appreciated.
Hi,
I have two varchar column in SQL.there im storing Month name
Lets say,
col1 col2
JAN APR
MAY DEC
Is there any possibility to use the sql query select * from tablename where DEC between (col1,col2)
I just wonder since it is varchar field and not date field can i use this syntax to compare ? or any other better way to achieve this?
Thanks for any response.
Regards,
RR
I have a table with the field end_date which is defined as a smalldatetime.
I have been unable to update this field using the SqlCommand object.
In my windows form I have a DateTime Picker and I am trying to get it's value into the db with no success.
I have tried
command.Parameters.Add ("@end_date",SqlDbType.DateTime);
command.Parameters["@end_date"].Value = dtpEndDate.Value;
I have also tried variations of
command.Parameters.AddWithValue ("@end_date",dtpEndDate.Value);
Please assist if you can.
I'm doing DTS, Here is one of my Sql Query in DTS
Select * From ZT_DailyRpt_Detail Where isNumeric(TenDayDate) = 1 And TenDayDate < Convert(varchar(10),DateAdd(Month,-CAST ((SELECT keepmonth FROM zt_databackup a, zt_biller b WHERE a.companycode = b.companycode AND zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)
the sub query �SELECT keepmonth FROM zt_databackup a, zt_biller b WHERE a.companycode = b.companycode AND zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12
for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month
if sub query return "6" and I do DTS on 2007/07/29 , will select date which TenDayDate< 2007/01 not TenDayDate<2007/01/29 ( don't want to check the day of date)
does my query correct? if not can you correct it for me? thank you very much
In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.
For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.
Are there any more developend ideas on this question?
Many thanks
Mike Thomas
hi all, the field type :datetime and smalldatetime, i still can't understand.everytime when i inserted the data to the db, i also get the error message "System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."i must change the field type to string, so that i can insert data my codetxt_datetime.text = '5/2/2006'insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason???thank you!!
View 4 Replies View RelatedI'm trying to extract the time part of a smalldatetime field in a usprocedure to return just the time. I've used Datepart() to get the hours or minutes but you can't get both without getting messy. Anyone know a way to keep it neat & simple?
Thanks, archie
Hello everyone,
I am using DTS to transform data from foxpro.when it meets the date of NULL,it fails and says"Insert Error , column 4( 'Coloumn_name ',DBTYPE_DBTIMESTAMP), Status 6 data overflow ".
And then i try to open the DTS Transform Data task and do a preview of the data from the 'Source' side.The Null values are treated as "1899-12-30".
After reading something ,i try to write an ActiveX scripting Task to solve this problem using the function of "IsDate()",but it fails with exceptions.
Now I don't know what to do.could anyone give me some suggestion?Anything will be appreciated!ThankX in advance!
How does one update the time part of a smalldatetime field...?
2006-11-16 20:12:00 ---> 2006-11-16 16:30:00
2005-06-01 18:19:00 ---> 2005-06-01 16:30:00
I have tried using the datepart but I'm doing something incorrectly.
thanks,
Jonathan
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)
I write code
select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)
it will like
select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)
→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30
but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
pleae help... thank you very much
Hello - I have a column in a table (SQL 2005 EE) with a Data Type of smalldatetime and a Default Value of getdate(). When I insert a record from my webpage the new record contains the correct date via getdate(). However if I update the record from my webpage the date of the record then becomes NULL. Is this normal? Is there anything I can do about this from sql server? I am inserting/updating via an formview and ODS, using standard insert/update methods.
Cheers
Marco
Hi,
I am facing problem while inserting a Null value into a smalldatetime datatype field in sql server 2000 using code in vb 6.0
Error as : Type mismatch.
Kindly let me know how to insert Null or blank (dtDate = "") into a column.
Regards,
Srinivas Alwala
In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004instead of 1/31/2004 10:30:25 AMHow can I do this in a stored procedure?lq
View 2 Replies View RelatedFrom 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
hai friends,
iam doing a project in .net and using sql server.
i need to compare only month and year part in datetime type to retrive data.
1)retrive unique year and its months available in the database.
like may 2008
apr 2008
mar 2007
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
I am a bit of a SQL Server newbie and have a question. I'm trying to compare two text fields. Both are 56 character fields and are a company name (one company want to see how much customer overlap they have with a newly acquired company). As you can imagine the names are a bit different in each ABN AMRO versus ABN AMRO INC. I tried comparing the first 6, 7, 8 characters with some success. Is there a more advanced way to do this? I appreciate the help.
View 2 Replies View Related
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.
Heres what I am doing:
I created a table called keywords that contains a reference for each product.
keyword -> varchar(100)
items -> Text
keyword data example:
[keyword] [items]
car 1, 3, 5, 7
blue 3, 5
compact 1,7
I am not using clustered index.
To search basically I run the "AND" or "OR" to select the keywords I want to target.
I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:
looking for car compact using "AND" clause
result = 1
looking for car compact using "OR" clause
result = 1,3,5,7
There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.
I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.
In addition, in this scenario, how should a clustered index help?
Thanks
Rodrigo
Hi Guys,
I have a report where some fields are blank, i need to put 0 when the field is blank, i tried the following
: IIf (Fields!X.Value = System.DBNull, 0, Field!X.Value)
this is complaining about System.DBNull is a type in system and cannot be used in comparison, what other types of fields can I use here to compare against Empty data?
is there anything basic I am missing?
I have 2 database, databaseA and DatabaseB with same table structure.
I change lot of table structure in databaseB.
So i want compare what field that i add or delete in databaseB.
it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.
How can i do that?
How can I create a field in a view that takes a date field andconverts it to a month?For instance, if I have a field called "CreatedOn" with a date of'2/22/2007', how can I create another field called "Month" that willsay "February"? Is there a built-in SQL function that does this (likeusing CONVERT) or do I need to write my own?Thanks!Lisa
View 1 Replies View RelatedHello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
Hello All,I've got a DATETIME field, and it includes hour:minutes:second data. I want to do selects where I can simply match on the month, day and year. For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken. Any suggestions?Thanks! -Josh
View 2 Replies View Related 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..... ?
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View Relatedfollowing table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
Most of the data is in one table.Â
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',Â