Validating A Date Masked As A String.
Jul 8, 2006
I'm trying to import a text file with a column that is actually a date value, like this "19550218" (yyyymmdd).
What is the best way to test if the string-date values in this column represent valid sql dates? Should I use SSIS to copy that value into an actual sql date field and see if an error is generated? I have tried using the VB.NET
"System.Datetime.ParseExact"
function in a Script task but found ParseExact less than reliable.
TIA,
Barkingdog
P.S. In fact, thought not speedy, I think that SSIS should come with a number of datatype validation routines "out of the box". Importing clean data is relatvely easy. It's handling the exceptional cases, like above, that really burn up my time.
P.P.S An before some says "regular expressions" I don't know enough about them to use them wisely.
View 1 Replies
ADVERTISEMENT
Apr 8, 2008
How can I validate the date that is coming across in a 8 byte character field via an expression? It will either be a valid date or 0. If it is zero I want to make it a null.
View 1 Replies
View Related
Jan 22, 2007
Hello,
My problem is with Validating Date parameter filed, when I user enters wrong (assume user is entering date instead of selecting from date picker). Where following are scenarios I would like to validate.When user enters non-date.
When user enters 30/feb/yyyy.When user enters 29/feb/yyyy. (for non-leap year).When user enters 31/mm/yyyy. (for months does not includes day 31)
Could you explain in a little more detail how to accomplish this?
thanks in advance,
Ramesh KS
View 3 Replies
View Related
Jan 15, 2007
Hi,
I have created a report in SQL reporting using serveral parameters including a date parameters called
StartDate EndDate
Now my requirement is, in ReportViewer I need to validate in such a way where
"StartDate" should not be greater than "EndDate".
So kindly help me out by giving me possible solution for the above issue.
thanks in advance,
Ramesh KS
View 1 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Feb 15, 2008
Hi,
I Have this simple convertion in a Script component
Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4))
this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format.
but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server.
How do I convert the string to date not depending on the SQL server language.
thanks.
View 2 Replies
View Related
Jan 8, 2013
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
View 14 Replies
View Related
Mar 12, 2007
Hi,
Im having a problem with a SQL string. Im not that great a building them and just need a bit of help. For example, my table has many fields, the 2 i need to concentrate on are; "Date1", and "Date2", these fields are VarChar(8). I also have a webpage thats going to build a report, that has 2 textboxes in with 2 dates, these textboxes are being used as a Date range (From > To). When a user submits the form, the query needs select all dates similar to this kinda thing (from >= "Date1" and to <= "Date2") if that makes sense. My Date format is (DD, MM, YY) BTW.
Thank you
Help is much appreciated.
View 2 Replies
View Related
Aug 15, 2013
I'm currently working on a project, i have a source of data that exports the date as a sting.
This data needs to go to a date field.
Here are some examples.
Mon 4 Feb 2013, 17:00
Fri 28 Jun 2013, 14:51
Mon 4 Feb 2013, 16:58
Mon 29 Apr 2013, 09:01
Fri 5 Apr 2013, 01:05
Mon 4 Feb 2013, 17:06
I've wrote this and it is a work in progress.
DECLARE @string VARCHAR(30)
SET @string = 'Fri 28 Jun 2013, 14:51'
SELECT @string
SELECT CONVERT(DATE, SUBSTRING(@string, 5, 2) + '-'
+ CASE WHEN SUBSTRING(@string, 8, 3) = 'Jan' THEN '01'
WHEN SUBSTRING(@string, 8, 3) = 'Feb' THEN '02'
[Code] .....
View 4 Replies
View Related
Oct 20, 2006
I have the following query which I converted from MS-Access. Its showing error due to concatenation of string and date after I converted Date() to GETDATE()
SELECT
tblCurrent.AutoNumber,
tblCurrent.CompanyName,
-(TotalAmount) AS Amount,
GETDATE() & ' Recd ' & [ParticipantInfo] AS Comment,
'No' AS RefundToBeProcessed,
tblCurrent.Department,
FROM tblCurrent
How do I solve it?
View 6 Replies
View Related
Dec 7, 1999
I'm using SQL 7.0 SP1.
I am using DTS to read in a date with the format of YYYYMMDD. I am trying to convert this date to MM/DD/YYYY and then use the CDate function with the following code to load it into a Datetime column in SQL Server:
Function Main()
strDate = DTSSource("Col002")
strYear = Left(strDate, 4)
strMonth = Mid(strDate, 5, 2)
strDay = Right(strDate, 2)
strHoldDate = strMonth + "/" + strDay + "/" + strYear
DTSDestination("DateName") = strHoldDate
Main = DTSTransformStat_OK
End Function
My DTS package will execute without errors, but it does not add the row. I have been successful using CDate when the source date is in the format MM/DD/YYYY.
Also, do you have any tips on how to debug DTS? How to see what's in a variable, etc.?
Thanks!
Lisa
View 1 Replies
View Related
May 5, 2008
Hi again, I am having troubles inserting a datetime value in a table to a string. what iw ant to do is have it be sent in an email. its an attendance email. here is the code i have right now:
select @summaryreport = isnull (@summaryreport + '; ',''+ char(13))+ '<BR>' +'Instructor '+instructor + ' Student ' + Fname + ' ' + Lname + ' - ' + classname +' ' + classdate from #tabledata
i get this as an error
Msg 241, Level 16, State 1, Line 92
Conversion failed when converting datetime from character string.
View 1 Replies
View Related
Oct 29, 2014
In our ERP system we have a field which is a date-picker in the user front end, but the value is stored in an NVARCHAR field and not always consistently. How can I convert this to a date (preferably in the format YYYY-MM-DD HH:MM) that I could use in a calculation?
select code, spec_value from spec_checklist_remind where spec_checklist_id = 17
code spec_value
------------ -----------------------------------------------------------------------
05MC0001 22/07/2014
05MC0002 23/07/2014
06MT0001 01-May-2014
06MT0002 01-May-2014
06MT0006 01/05/2014
06MT0007 01-May-2014
06MT0008 01/05/2014
View 5 Replies
View Related
Aug 12, 2005
Hello,I try to convert a pseudo datetime string into a date. In Oracle I can doto_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?thanks and regardsMark
View 2 Replies
View Related
Mar 17, 2008
I am running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:
-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error.
Any suggestions? I would prefer not to have to change every stored procedure we have which may do this.
Thanks for any help.
MaryKate
View 6 Replies
View Related
Apr 6, 2006
Having some trouble converting strings to dates. I am using a .dtsx package and have figured out how to use a derived column to convert the string to a new string which is a valid date. The problem is now converting the column data type to date. I insert a new data conversion step to convert the vchar field to date, then when it runs it fails on this step. Error is
The conversion returned status value 2 and status text "The value could
not be converted because of a potential loss of data."
If I leave out the data conversion step and run the package, then manually change the data type to date it warns with the same message, but if I accept it converts correctly.
Here is my derived column code
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
Please Help
View 9 Replies
View Related
Apr 16, 2008
I have a column llike this 041008 (char datatype). I want to convert this to date datatype. how to do this.
Thank you.
View 6 Replies
View Related
Sep 21, 2006
I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.
Now in my SELECT statement, I have this:
SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @startdate) AND (TLMST.INDT <= @enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER
Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.
Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information
View 5 Replies
View Related
Nov 19, 2007
Hello All again,
I have another question, I would like to validate the data in a sql table before my code runs and inserts duplicate information. Can anyone help out with this via example?
View 2 Replies
View Related
Jul 30, 2004
I have one table called product 1 and the second table is called product 2 i have DirNo, ProdNo, UpdCode and BranchOffice as fields i whant to loop trought every record in table product 1 and check in table product 2 if the record is the same and exist i f it does go to next record, if not delete the record in product 1 and move to the next record and so on. could someone please help me. iam very new at this.
thanks
View 4 Replies
View Related
Sep 23, 2007
This question is rather open ended. Basically, I'm wondering the different approaches people use when validating input into a stored procedure. The rest of my post just describes a rather simple approach I'm using, and some ideas I have, but I'm eager to know what others are doing. So, if you'd like to comment on this, feel free to do so without reading the following.
I often find myself calling a stored procedure, and needing to validate some of the inputs before proceeding with the rest of the tasks. Such as, making sure a matching record isn't already in the database before adding a new record. Ideally, this sort of validation will report back to the user interface immediately, so that the user can get real-time response to their form input, instead of the all-too-common approach of redirecting to an error page if something goes wrong in the database transaction.
It's also convenient, at times (though perhaps not efficient in all cases) to let your database perform business rules validation of inputs (let the user interface make sure data types are valid, and such), so that you duplicate less code in the event that the stored procedure is called in more than one context.
To address these issues, I've taken to making two stored procedures instead of one. Let the stored procedure be called AddRecord. I'd create that, as well as the procedure named AddRecord_Validate, which would take identical inputs as AddRecord, whenever possible. Nearly the first thing done in AddRecord would be to execute AddRecord_Validate. The user interface would also call AddRecord_Validate, and return any validation errors to the user interface.
This seems rather convenient to me, in many cases, but often it doesn't work as well as I prefer. It's not uncommon for me to end up performing the same queries in the _Validate SP as I do in the parent SP. For example, let's say I'm passing a parameter that I use to look up a record I plan to edit. In my validation, I query the database to verify that the record is found. But in the parent SP, I run the same query again in order to get the stored ID of the record I need to edit. This ends up duplicating queries, making the pair of procedures less efficient on the whole, as well as introducing the likelihood of bugs when code changes in one of the SPs, but not the other.
So I've been brainstorming other approaches. The first idea is to execute a single stored procedure, but when I want to run it in "validation" mode, I simply rollback the transaction. This would let me know if the stored procedure would have run with the specified input, but won't ultimately change anything. Unfortunately, I see some badness in this, such as the entire SP perhaps taking a lot longer to execute that simple validation would have, and side-effects such as incrementing Sequences, or locking the database, and basically just doing a whole lot more work during validation than needs to be done.
The next idea was to require that the user specify the "run mode" via a parameter, either 'validate' or 'run'. Then, all of the validation would be performed at the top of the stored procedure, and a simple IF statement would exit the SP before actually making any changes if it's run in 'validate' mode. Otherwise, it will continue, and do the real work. The only real downside I see to this is forcing the developer to deal with an extra parameter. And, perhaps, it's not really a "relational" approach.
So, what do the rest of you do?
View 5 Replies
View Related
Apr 14, 2008
hello
i am using visual studio 2008 to create an inventory management system and i am having trouble checking for a constraint violation. i have a partnumber colum in the database that needs to be unique and i am using datasets / tableadapters to interact with the database. when a user adds a new part number to the database i need to make sure its not already there
i have tried to use
catch ce as constraintexception
debug.print ce.tostring
end try
but the program still crashes with
System.Data.ConstraintException was unhandled ( see full error below)
no matter where i put the try statement it says its unhandeled
this error triggers when when i leave the combo box (when its validated) i have even tried placing it here
Private Sub PartNumberComboBox_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles PartNumberComboBox.Validating
Try
Catch CE As ConstraintException
Debug.Print(CE.Message)
Catch EX As Exception
Debug.Print(EX.Message)
End Try
End Sub
if someone could point me in the right direction id greatley appriciate it
System.Data.ConstraintException was unhandled
Message="Column 'PartNumber' is constrained to be unique. Value '36LDVRRP' is already present."
Source="System.Data"
StackTrace:
at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean fireEvent)
at System.Data.DataRow.EndEdit()
at System.Data.DataRowView.EndEdit()
at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData)
at System.Windows.Forms.CurrencyManager.set_Position(Int32 value)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.GroupBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
at System.Windows.Forms.Control.DefWndProc(Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Inventory_System.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
View 9 Replies
View Related
May 18, 2007
Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
View 2 Replies
View Related
May 13, 2004
i have a sql statement that i created in code and it is sending a query to the database
when i dim the variable a datetime variable it says that it cant convert it
if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String
strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear
If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID "
End If
If CreatedBy > 1 Then
strQueSearch = strQueSearch + " and t_Que.RepID =@RepID "
End If
If CreatedFor > 1 Then
strQueSearch = strQueSearch + " and t_Que.Manager = @Manager "
End If
If DateCreated <> "" Then
If DateCreatedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate "
ElseIf DateCreatedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate "
ElseIf DateCreatedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate "
End If
End If
If DateCompleted <> "" Then
If DateCompletedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete "
ElseIf DateCompletedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
ElseIf DateCompletedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete "
End If
End If
If DateStarted <> "" Then
If DateStartedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate "
ElseIf DateStartedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate "
ElseIf DateStartedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate "
End If
End If
If SearchType = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf SearchType = 1 Then
'Mail
strQueSearch = strQueSearch + " and t_Que.Mail=1 "
ElseIf SearchType = 2 Then
'Phone
strQueSearch = strQueSearch + " and t_Que.Mail=0 "
End If
If Completed = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf Completed = 1 Then
'Yes
strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
ElseIf Completed = 2 Then
'No
strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
End If
If Priority > 0 Then
If PriorityMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority "
ElseIf PriorityMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority "
ElseIf PriorityMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority "
End If
End If
If RemainingCalls > 0 Then
If RemainingCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity "
ElseIf RemainingCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity "
ElseIf RemainingCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity "
End If
End If
If TotalCalls > 0 Then
If TotalCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal "
ElseIf TotalCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal "
ElseIf TotalCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal "
End If
End If
If Bonus > 1 Then
strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID "
End If
If Keyword1 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' "
End If
If Keyword2 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' "
End If
If Keyword3 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' "
End If
If Keyword4 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' "
End If
If Keyword5 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' "
End If
'makes statement into sqlcommand
C.daQueSearch.SelectCommand.CommandText = strQueSearch
'var declaration
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BatchID", SqlDbType.VarChar, 12))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Manager", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@RepID", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDate", SqlDbType.VarChar, 20)) '<--- This is what,when i change to datetime, says it cant convert
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueStartDate", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDateComplete", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QuePriority", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueQuantity", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueTotal", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BonusID", SqlDbType.SmallInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword1", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword2", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword3", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword4", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword5", SqlDbType.VarChar, 50))
'data entry
C.daQueSearch.SelectCommand.Parameters("@BatchID").Value = BatchID
C.daQueSearch.SelectCommand.Parameters("@Manager").Value = CreatedBy
C.daQueSearch.SelectCommand.Parameters("@RepID").Value = CreatedFor
C.daQueSearch.SelectCommand.Parameters("@QueDate").Value = DateCreated
C.daQueSearch.SelectCommand.Parameters("@QueStartDate").Value = DateStarted
C.daQueSearch.SelectCommand.Parameters("@QueDateComplete").Value = DateCompleted
C.daQueSearch.SelectCommand.Parameters("@QuePriority").Value = Priority
C.daQueSearch.SelectCommand.Parameters("@QueQuantity").Value = RemainingCalls
C.daQueSearch.SelectCommand.Parameters("@QueTotal").Value = TotalCalls
C.daQueSearch.SelectCommand.Parameters("@BonusID").Value = Bonus
C.daQueSearch.SelectCommand.Parameters("@Keyword1").Value = Keyword1
C.daQueSearch.SelectCommand.Parameters("@Keyword2").Value = Keyword2
C.daQueSearch.SelectCommand.Parameters("@Keyword3").Value = Keyword3
C.daQueSearch.SelectCommand.Parameters("@Keyword4").Value = Keyword4
C.daQueSearch.SelectCommand.Parameters("@Keyword5").Value = Keyword5
Try
C.ndConnection.Open()
C.daQueSearch.SelectCommand.ExecuteNonQuery()
Catch ex As Exception
lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
lblMainError1.Visible = True
Finally
C.ndConnection.Close()
End Try
FillQSDG()' this fills the datagrid
End Function
View 8 Replies
View Related
May 27, 2006
I basic question but can someone help.
I have a SELECT statement, the result of which populates a
datagrid. The first column has consecutive dates in it and I want
to hyperlink each date to a seperate Javascript function (the
Javascript is created on the fly and is unique for each date). I
need a different function name for each function and so tried the date
but "/" is not allowed in the Javasript function name. I think
the easiest way will be to produce a new column with the date expresses
ddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy).
I tried :-
"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]
but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.
I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!
Many thanks
Mike
View 2 Replies
View Related
Jun 9, 2006
Hi;
I am attempting to import records from a text file.
This text file is a report file created by a mainframe routine, if this makes any difference.
When I attempt to transform the file via a VB Script it craps out when I use
the CDate(). An example of the script:
Destination("A_Date") = CDate(Source("Column1").
Some of the columns in the text file contain the string value "(Null)" and
some rows have that column containing a "01/05/06".
I have tried testing for the the string value "(Null)" but that doesn't solve my problem.
Are there any other VB functions that I could use ?
Thanks,
NeedSomeHelp
View 2 Replies
View Related
Apr 16, 2004
Hi,
Can anyone tell me how to convert date and time to string.
I got one field inside my database which contain data like below:
4/16/2004 10:19:01 AM
if i wan to call out a record which have to refer to the data above
Select *
From table_A
where field_date= ???????
what to fill in the ??????
Thanx
View 3 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Jun 12, 2008
I am trying to import a txt file that has a string for it's date. How do I change it over in the import wizard I've tried going to advance then changing from string to date or date time but I get an error.
View 14 Replies
View Related
Oct 12, 2013
To change string '24.04.2013' (Apr. 24, 2013 format of dd-mm-yyyy) into a date in format of mm-dd-yyyy, what's the easiest way?
View 5 Replies
View Related
