Date Formatting - Pull Information From Data String
Jul 9, 2013
'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'
Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:
cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)
...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -
So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] ....
Result: Need to Display [Total Service Time] in below Format:
SQL query: I am importing data from one source to another. The phone number has 3 variations and I want to standardize the information. I need to remove the "+1" or "1" from the leading characters of the phone number.
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select * into #temp_2 from #temp_1 a, Server2.DBa.dbo.table2 r where r.field1r = a.field1a and r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific: r.field3r looks like 000884 a.field3a looks like 17445000884 So- I just want to pull in the 000884 off of a.field3a
Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt.
I'd like to know if there is a way to replace everything between these two integers (well, actually we can say two words as they are in the string format) with a single comma (i.e ,) and remove everthing leading and trialing to these two numbers please ?
In short , I would like to see the above line after string manipulation as follows :
Here is the problem. I am trying to write this: strLink2 = "<a href='JavaScript:OpenFile(" & strFileName & ")'>" & strFileName & "</a>" to SQL and then return it to a datagrid when I want it.
Easy enough, and as such all is well except for the fact that the Javascript doesn't work. In the grid it gets written as JavaScript:OpenFile(myfile). I need to add the single quotes around the file name so it writes JavaScript:Open('myfile').
I now know many ways that do not work. Any help would be greatly appreciated.
Can someone please help me with this? I need a query that will pull clients that made payments last year but not this year. I need the query to use date parameters so I can select any date range for the past year and the current year. I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables. Date range is based on the tblPaymentReceipts.PaymentDate tblClients, ClientID tblPayment, PmtID, ClientID, Paystartdate, Paygroup tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT FC.CASE_ID, UPPER(FC.CASE_NUMBER) AS CASE_NUMBER, UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE, FC.CASE_STATUS_DESCR, FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
[Code] ....
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
I have what should be a simple string formatting issue -- removing all alpha characters from a phone number. T-SQL below is simple and works just fine:
declare @home_phone as char(14); set @home_phone = '(123)979-3206'; set @home_phone = REPLACE(@home_phone,'-',''); set @home_phone = REPLACE(@home_phone,'(',''); set @home_phone = REPLACE(@home_phone,')','');
The condensed version below works equally well:
set @home_phone = REPLACE((REPLACE((REPLACE(@home_phone,'(','')),')','')),'-','');
Either script above returns correct result:
select @home_phone; Result = '1239793206'
HOWEVER, within SSIS Derived Column expressions, this function fails to remove parentheses. First of all, Expression Builder doesn't like outer single quotes (turns red) which I resolved by using double quotes resulting in the following expression:
Unfortunately, this expression fails to remove parentheses and instead returns: '(123)9793206'
I've tried several different permutations--no inner single quotes; adding more inner single quotes; etc., but so far no success. Any suggestions would be much appreciated!
I have two tables and I am trying to pull a value closest to another date. There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
I am trying to upload data from CSV to Sql table. I have a column as 'arrived_date' value '13:45' etc and while trying to load data i am getting error as "data conversion failed ,truncation may occur while loading data". In flat file connection this column datatype is string but in my table datatype is as time(). There is a error with conversion. I tried to change data type in advanced editor but no use. Using data conversion after flatfile makes my error disappear but it is giving error right at the file not even going through from file?
The source is a flat file with a column where dates are stored as: 07/12/2006 11:35am. In some cases the column is blank.
I need to import this into a table with a datetime column. In the data flow task, I get the error that conversion b/w String and DB_Timestamp is not allowed. First question is why does SSIS think its a DB_Timestamp column? Shouldnt it be DB_Date or DB_Time. Anyway, when I add a Data Conversion Task in the Data Flow, and cast to the source column to either DB_TimeStamp, DB_Date or DB_Time I get an error.
I'm currently getting a date in this format 2001-10-08 10:35:45 (yyyy-mm-dd hh:mm:ss) how would I convert the date to this format 2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
I am trying to format a date value as XX/XX/XX when I place a "d" in the format property it formats it as XX/XX/XXXX any ideas on how to change it to XX/XX/XX?
How can I format the current date as yyyy-mm-dd and display it in a text box on the report. The FormatDateTime function only allows certain NamedFormat for the short and long dates but does not satisfy the above requirements. Any ideas?
I have the following table called BADSANTA: varchar(30) datetime NAME WHENBAD OJ Simpson 2007-1-12 xx:xx:xx:xxx OJ Simpson 2007-4-2 xx:xx:xx:xxx Monica Lewinsky 2006-7-4 xx:xx:xx:xxx Monica Lewinsky 2006-10-31 xx:xx:xx:xxx Bill Clinton 2006-7-4 xx:xx:xx:xxx Bill Clinton 2006-10-31 xx:xx:xx:xxx Bart Simpson 2006-11-2 xx:xx:xx:xxx Bart Simpson 2006-2-25 xx:xx:xx:xxx Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
I need a script to pull the above records ONLY if the date repeats more than one within the same SEQ number. The proper script would output as follows:
Helo All - I would greatly appreciate some help formatting the values for my sproc. I need to call a sproc and pass it the values StartTime and EndTime from a web form. The web form uses 3 DDLs for the Start Hour, Start Minute and Start AM/PM as well as 3 DDLs for the End Hour, End Minute and End AM/PM. When I call the sproc, I get the error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Here is the sample column data: 2:06 PM which is stored as nvarchar in the sql db Here is the Sub and the Sproc, Sub Protected Sub btnDateQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDateQuery.Click pnlFiltered.Visible = True pnlShowAll.Visible = FalseDim StartTime As DateTime Dim EndTime As DateTimeStartTime = ddlFromHour.SelectedValue + ":" + ddlFromMinute.SelectedValue + ":" + "00 " + ddlFromAMPM.SelectedValue EndTime = ddlToHour.SelectedValue + ":" + ddlToMinute.SelectedValue + ":" + "00 " + ddlToAMPM.SelectedValueStartTime = CDate("2:07:00 AM") EndTime = CDate("2:07:00 AM")Dim Query_TA As New dalDataFeedsdefsTableAdapters.spFilterExpectedEndTimeTableAdapterDim returncode As Integer = 0 Query_TA.GetExpectedEndTimeData(StartTime, EndTime) Dim Select_TA As New dalDataFeedsdefsTableAdapters.spDynamic_Basics_TblTableAdapterDim dv As New DataView Try dv = Query_TA.GetExpectedEndTimeData(StartTime, EndTime).DefaultView()Catch ex As Exception Response.Write(StartTime + " - " + EndTime) End TryWith DataFeedGridView .DataSource = dv .DataBind() End With End Sub SPROC ALTER PROCEDURE [dbo].[spFilterExpectedEndTime] -- Add the parameters for the stored procedure here @StartTime datetime,@EndTime datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select * From tmpBasics where convert(datetime, ltrim(rtrim(starttime)), 114) >= @StartTime and dateadd(mi,convert(int, duration), convert(datetime, ltrim(rtrim(starttime)), 114)) <= @EndTime order by convert(datetime, ltrim(rtrim(starttime)), 114) END Thanks, Pat
I converted an Access Database to SQL Express. The dates were converted to datetime I'm using VWD 2005 Here is the source of my date and the query. sqlDate = (DateTime.Now.AddDays(-7)) sqlTxt = "SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate + "', 101)) ORDER BY Service_Orders.SDate_Entered DESC" This retrurns 0 records. sqlDate = 11/28/2005 12:23:27 AM from the function above. The query will return records with : sqlDate = "2005-11-01 21:56:20" I tried changing the CONVERT(DATETIME, '" + sqlDate + "', 1XX from 100 to 120 with no luck I know this must be an easy fix, but it is beyond me. I need to know how to 1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20" or 2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly Thanks for any help in advance! Bill
I have around 1000 records each with two dates in a database in MSDE on my PC. I need to move the data to an on line SQL server and have tried to use Microsoft Web Data Administrator to do this. I can export the data from the MSDE to an SQL file but it will not import because the date format in the SQL file is "dd,mm,yyyy".
If I export from either the MSDE or the SQL server both produce files with date in the format "dd,mm,yy" and yet I cannot import either!?!
It is impractical to change all the dates by hand. I am on a budget and do not have access to anything other than free software.
I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end? Thanks
I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view? Thanks
