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 - ?
View 3 Replies
ADVERTISEMENT
May 6, 2008
Sorry if this is rudimentary.
I need a select statement to retrieve what version of sql server is running and whether it is enterprise or standard.
I know I could just right click but I have hundreds of servers to go through so this should save alot of time.
Thanks,
Kraig
View 2 Replies
View Related
Jun 20, 2015
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:
But Some values are repeating ....
View 2 Replies
View Related
Sep 23, 2013
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.
example:
13035554444
+13035554444
View 4 Replies
View Related
Jul 6, 2015
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
View 9 Replies
View Related
Dec 13, 2007
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
View 2 Replies
View Related
Mar 19, 2015
I am trying to figure out how to pull only a certain character string.
orig string varies in length (Varchar 50)
ex: MID - Proposed - Prime
ex: MID - NotProposed - NotPrime
I want to keep the first 6 characters 'MID - '
as well as up to the 3rd SPACE (not including the space)
so
ex: MID - Proposed
ex: MID - NotProposed
View 1 Replies
View Related
Mar 26, 2008
'conn info
Const localConnectionString As String = "Data Source = TestDB.sdf"
Const serverConnectionString As String = "Server=myserver;database=BoxDB;Integrated Security=True;"
Sub CreateUploadOnlyTable()
Dim rda As SqlCeRemoteDataAccess = Nothing
Try
rda = New SqlCeRemoteDataAccess()
rda.LocalConnectionString = localConnectionString
rda.InternetUrl = "http://myserver/BoxCE/sqlcesa30.dll"
rda.Pull("temp_Swipes", "SELECT * FROM Swipes WHERE 1 = 0", serverConnectionString, RdaTrackOption.TrackingOnWithIndexes, "Swipe_Errors")
Catch ex As Exception
MsgBox(ex.ToString)
SaveTextToFile(ex.ToString, "CreateUploadOnlyTable.txt", "RDA Error!")
Finally
rda.dispose()
End Try
End Sub
when i try to run it on my CE device i get an error which i have no idea about and can't resolve via google'ing.
"multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. no work was done"
My aim here is just a basic Pull (but containing no data hence the 'WHERE 1=0')
Any answers to this error? is it my conn string? i did have conn string errors earlier and am guessing they are linked.
View 5 Replies
View Related
May 2, 2008
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.
View 4 Replies
View Related
Apr 14, 2008
Hi, I've a string like :
'...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,'
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 :
'123456789,876564532'
Thanks for your help.
View 18 Replies
View Related
Jul 26, 2004
I am well stuck in the mud right now.
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.
View 5 Replies
View Related
Dec 9, 2007
Is there anything in SSIS that enables simple string reformatting?
For example I'd like to convert the character string 1234567.89 to 1,234,567.89.
I used to do this with an edit pattern in Cobol, but I can't see anything in the Expressions editor....
View 4 Replies
View Related
Dec 17, 2006
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
View 6 Replies
View Related
May 5, 2014
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
View 9 Replies
View Related
May 27, 2008
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:
REPLACE(home_phone,"-","") + REPLACE(home_phone,"'('","") + REPLACE(home_phone,"')'","")
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!
View 15 Replies
View Related
Sep 12, 2007
I have a string in which I am combining several fields. Is there a way to add formatting to the string as well
="Baud: " & Fields!BAUDRATE.Value & " DL Status: " & Fields!COMMSTATUS.Value
Example, I want my fields to appear in bold and string in normal.. I can't do this in an individual textbox due to space constraints
View 1 Replies
View Related
Apr 21, 2015
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?
TableA
PA_IDEval_Date
12015-01-08 00:00:00.000
22015-01-25 00:00:00.000
TableB
IDPA_IDDate Stat
112015-01-15 00:00:00.000 14
222015-01-28 00:00:00.000 8
312015-01-16 00:00:00.000 2
422015-01-029 00:00:00.000 3
512015-01-09 00:00:00.000 11
612015-03-14 00:00:00.000 14
Expected Results
PA_IDDate Stat
12015-01-09 00:00:00.000 11
22015-01-28 00:00:00.000 8
View 8 Replies
View Related
Nov 30, 2015
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?
View 10 Replies
View Related
Jul 13, 2006
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.
Please help. What am I doing wrong.
Asim.
View 7 Replies
View Related
Apr 16, 2008
How do I pull only the latest shipdate record from a multiple record key?
I tried the following, but it doesn't pull only the lastest date record:
Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate
Thanks in advance.
View 7 Replies
View Related
Oct 10, 2001
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.
Thanks in advance,
Ed
View 2 Replies
View Related
Aug 6, 2004
Hi,
I need to know how i can format the date so that the query results will come out as the format mm/dd/yy hh:mm:ss?
Thanks alot.
View 1 Replies
View Related
Mar 7, 2008
Hello everyone,
I have a date field called book_flag_date of varchar data type.
The values in the table look like this:
3/4/2008 14:32:59
OR
3/4/2008 14:9:0
The issue arises when I am ordering by. I want the value
3/4/2008 14:9:0 to format to 3/4/2008 14:09:00
Thank you very much for your help.
View 7 Replies
View Related
Sep 27, 2007
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?
thanks in advance
View 1 Replies
View Related
May 25, 2007
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?
View 3 Replies
View Related
Feb 13, 2007
My problem is with the paramater format date.
My code and data in SQL are showing the date correctly as dd/mm/yyyy.
When I run report in SSRS a couple of columns show the date mm/dd/yyyy.
The format option in all cells are set to 'd'. There is no difference between the properties on these cells.
The dates themselves seem to go wrong on the date "31/12/4000" and returns them as "12/31/4000".
Any help wqould be appreciated
View 1 Replies
View Related
Oct 30, 2007
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.
Thanks in advanced.
View 4 Replies
View Related
Jan 30, 2014
I have the following table:
SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
124 FEB-28-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
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:
SEQ DATE
123 JAN-01-2013
123 JAN-01-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
125 MAR-05-2013
View 2 Replies
View Related
Jan 24, 2008
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
View 1 Replies
View Related
Dec 5, 2005
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
View 2 Replies
View Related
Jan 30, 2006
Hi,
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.
Can anyone advise me of the best way forward.
Thanks in anticipation.
Mike
View 8 Replies
View Related
Jan 19, 2004
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
View 12 Replies
View Related
Jan 20, 2004
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
View 14 Replies
View Related
