I'm looking for a TSQL function or technique that when provided a date time as string and also a format, it must return a valid tsql datetime object/variable.
for example somethig like
GetDateTime('20071010', 'YYYYMMDD') or GetDateTime('10/10/2008', 'MM/DD/YYYY')
Essentially I want to get away from Implicit conversion from string to datetime. I want better control over this to ensure we dont run into any issues when our code runs on systems configured to be on various cultures.
I can even live with a few fixed formats and not being able to support arbitrary formats.
I am very new to SSIS. I need a package to iterate through files in folders and subfolders, evaluate the date of the file and delete them if they are over 90 days. I am using the following as a guide. The reason I am not using a maintenance plan for this is that I did not see a way to use a built in maintenance plan task to copy files. I already built a package that copies files to the destination. Now I need to delete files in the destination that are over 90 days old.
[URL] ...
I have a File System Task to delete files. I have a Script Task with a precedence constraint where I need to build an expression on the constraint between the Script Task and the File System Task for the delete.
I have database backups in the format of SQLInstanceName_DatabaseName_BackupType_YYYYMMDD_XXXXXXX.bak.
I created a variable to store the filename and I need an expression that will convert a file name like :
MySQLInstance_MyDB_DIFF_20150803_1700000.bak into 20150803, compare it to getdate -90 days.
I created another variable for the Max File Age. I built the below but when I click on Evaluate Expression I get an error stating, "The expression might contain an invalid token, an incomplete token, or an invalid element."
can any help me why the same exact code works in vb.net but not in C# . case is very simple I have a table called MenuItems which has menu items and a table sizeandprice which has price for each menuitem based on the size. simple case of 1 to many relationship between menuitems table to sizeandprice table. I am trying to display in a gridview control couple of fields from menuitems table and have another template field in which i am display price and size field from the child table which is sizeand price. so basically this is how my code looks like Page in vb works fine <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then Dim ds As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource) ds.SelectParameters("fkMenuItemID").DefaultValue = GridView1.DataKeys(e.Row.RowIndex).Value End If End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]"></asp:SqlDataSource> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound"> <Columns> <asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False" ReadOnly="True" SortExpression="MenuItemID" /> <asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" /> <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" /> <asp:TemplateField HeaderText="Size And Price"> <ItemTemplate> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)"> <SelectParameters> <asp:Parameter Name="fkMenuItemID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2"> <ItemTemplate> <%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br /> </ItemTemplate> </asp:Repeater> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
</div> </form> </body> </html>
if you would notice that in order populate my repeater with childrows i need to know the menuitemid from the parent row which i do in rowdatabound event however same code in C# does not render any values for my item price and size defined inside the repeater control. here is how the c# page looks like
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
OK, this is a simple question for you, but i am a newbie to SQL2000, and i need to find out which Service Pack we are currently running. Could anybody please give me a short reply :)
I've a table with values that have the '-' char, ex:LO-AS-1.I'm trying to search the table for that value but it returns more records since it breaks the search term into several others:
SELECT * FROM sys.dm_fts_parser ('"LO-AS-1"', 2070, 0, 0) returns: lo-as-1 lo as - noise word 1 - noise word nn1 - noise word
Is there anyway of searching for the exact word? With LIKE it works fine but it's very slow....
Hi. I have some contracts and i want to find for every specific contract (every contract can be repeated 2-3 or more times (same policynumber) the contracts that have the same date. That's the contracts INSIDE the contract that have the same data. I was trying something like select distinct m.masterpolicyno from multicontract m where m.category=3 and m.issuedate = m.issuedate but it think i must use "having" and "in" but i can't figure it out. Any help? Thanks.
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
Hi all, i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is: String was not recognized as a valid Boolean. how can i find the exact line causing the error? thank you -shane
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
How to search a string from the given values. i want to search a string "Session" from the given column of results.. it is separator by comma. i want only 2 results from the given value... if i'm writing as like keyword it will return 4 but i need only the exact match of string.. _______________________ The Result should be Session,Study Patterns, session, asp.net _______________________ But the Result is coming as Session study, usercontrol Session, study Technical Session, Asp.net Patterns, session, asp.net ________________________ anyone tell the solution
books catalog, education, best books Birthday, Party Gopi Session study, usercontrol Session, study Holiday Technical Session, Asp.net Patterns, session, asp.net day, party events for Lords, daily thing events manager events things meeting, administrator marriage project ,event, demo madurai ,event demo, event calendar rangoli, event Demo Project event project
Hi can anybody give me a simple example to check exact match word using sql sever Ex: my word is like this : welcome to sql sever2005 Now i want to find server how it is possible? I tried like this select * from test where content like %server% . but is gives me possible result. But i dont want like this if i have an exact match with that key word then only i want possitive result. Actually there is no word with server, i'm having only server2005 . So how can I get it?
I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).
For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:
Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?
I have a report that displays many duplicate rows where all fields in several rows are exact duplicates. I would like to know how to make it so that the entire row does not show up if it is an exact duplicate of another row. Any ideas? Thanks.
In my SSIS package I have a text file source that I am mapping to a destination table. I have an error component that logs any row level errors and have noticed that it is not logging the correct field. I know this because I have a few different sources that submit the same files and have looked at the source of both. THE ONLY DIFFERENCE in the one that works versus the one that does not is that 2 of the 25+ columns are switched. I would not think this would matter because field A in the text file is mapped to field A in the database.
Does the order in which the fields come into the SSIS package matter?
I have a very basic Lookup in my SSIS package that looks up against two columns and outputs a row to a table. Now currently if there is no exact match, it writes a null in my destination table. How do I simply drop all those rows that dont produce an exact match? I tried using the 'Ignore' error output, but with that it writes NULLS into my destination table. With the 'Redirect' it is looking for a place to redirect the error (NULL) rows, and I dont want to deal with the hassle or writing these NULL values to a file or table just to delete them afterwards. I just simply want to forget about all those rows that dont produce an exact hit and only fill in the destination table with those that do produce a hit. How can I drop these lookup rows that dont produce an exact match?
Session1 Session2 Session3 Session4----------------------------------------------------------------------------------------- SQL Server-Part1 SQL Server-Part2 ASP.Net CSS C# SQL Server-Part3 ASP.Net Javascript I have set the Full-Text to all the columns. For searching i wrote the below query SELECT * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"') My Result expectation is: The First Record should come. But the result of the query bring the two records. It see the "SQL Server" string also. I need to find the exact word. How to do it? Please answer me as soon as possible. Ganesh.
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example: set MyUniqueNumber = 20071101190708733 ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following: 1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2. select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
Hello,I have a table that has a name field with the following datajohn doejohn doe smithjohn d smithI need to separate the first, middle and last names into separatefields. ex. first varchar (20), middle varchar (20) and last varchar(20).I am testing the process that I have to follow by doing the followingcreate table names (name varchar (40),first varchar (20),middle varchar (20),last varchar (20))insert into names (name)values (john doe smith)I get an error message when I run the following query:update bset first = substring(name, 1, (charindex('', name)-1))The error message is:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.The statement has been terminated.Does anybody have any suggestions?TIAja
I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.
I am wondering what would be the best method of doing this, or if anyone has an example.
I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.
Help!
Select '%,' into tblclients as strfirstname from tblclients
I know this won't work, but I want to show you all I'm at least trying!
This script parses the @@VERSION global variable into individual columns.
I developed it because I wanted to be able gather standard info on all versions.
I know there are functions for a lot of this, but only starting with SQL 2000.
It seems to work with all versions of SQL Server from 7.0 through 2005.
I haven't tested with 6.5 and before or 2008, because I don't have either available.
Please report any problems you see.
Edit: 2007-7-31 1. Changed SQL_SERVER_MAJOR_VERSION to varchar(20) 2. Added code to create a view named V_SQL_SERVER_VERSION 3. Added four new columns to the view: SERVER_NAME, value from @@servername SQL_SERVER_MAJOR_VERSION_NUMBER, Example: 9 SQL_SERVER_VERSION_NUMBER, Example: 8.0020390000 WINDOWS_VERSION_NAME, Example: 'Windows 2000'
Edit: 2007-8-2 Changed SQL_SERVER_MAJOR_VERSION to varchar(40)
from ( select zz = stuff(yy,charindex(Char(10),yy),1,'#3#') from (
select yy = stuff(xx,charindex(Char(10),xx),1,'#2#') from ( select xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#') from ( select VERSION = @@VERSION ) a ) a1 ) a2 ) a3 ) a4 ) a4
Results:
SQL_SERVER_MAJOR_VERSION SQL_SERVER_VERSION SQL_SERVER_PLATFORM SQL_SERVER_EDITION WINDOWS_VERSION WINDOWS_BUILD WINDOWS_SERVICE_PACK ------------------------ -------------------- -------------------- ------------------------------ -------------------- -------------------- ------------------------------ 2000 8.00.2039 Intel X86 Standard Edition Windows NT 5.0 Build 2195 Service Pack 4
(1 row(s) affected)
drop view [dbo].[V_SQL_SERVER_VERSION] go create view [dbo].[V_SQL_SERVER_VERSION] as select SERVER_NAME = @@servername, SQL_SERVER_MAJOR_VERSION, SQL_SERVER_VERSION, SQL_SERVER_MAJOR_VERSION_NUMBER = convert(int,floor(convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4)))), SQL_SERVER_VERSION_NUMBER= convert(numeric(20,10),( convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4))*1000000+ convert(numeric(20,10),substring(SQL_SERVER_VERSION,6,30)))/1000000), SQL_SERVER_PLATFORM, SQL_SERVER_EDITION, WINDOWS_VERSION_NAME = convert(varchar(20), case when WINDOWS_VERSION = 'Windows NT 5.0' then 'Windows 2000' when WINDOWS_VERSION = 'Windows NT 5.1' then 'Windows XP' when WINDOWS_VERSION = 'Windows NT 5.2' then 'Windows 2003' else WINDOWS_VERSION end), WINDOWS_VERSION, WINDOWS_BUILD, WINDOWS_SERVICE_PACK from ( select SQL_SERVER_MAJOR_VERSION = convert(varchar(40),substring(L1,1,L1_BREAK_1-1)), SQL_SERVER_VERSION = convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))), SQL_SERVER_PLATFORM = convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))), SQL_SERVER_EDITION = convert(varchar(30),substring(L4,1,L4_BREAK_1-1)), WINDOWS_VERSION = convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))), WINDOWS_BUILD = convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))), WINDOWS_SERVICE_PACK = convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))), VERSION = VERSION from ( select VERSION, L1_BREAK_1 = charindex(' - ',L1), L1_BREAK_2 = charindex(' (',L1), L1_BREAK_3 = charindex(')',L1), L4_BREAK_1 = charindex(' on Windows',L4), L4_BREAK_2 = charindex(' (',L4), L4_BREAK_3 = charindex(': ',L4), L4_BREAK_4 = charindex(')',L4), L1, L4 from ( select VERSION, L1 = convert(varchar(100), rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server',''))) ) , L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))
from ( select VERSION, zz = stuff(yy,charindex(Char(10),yy),1,'#3#') from (
select VERSION, yy = stuff(xx,charindex(Char(10),xx),1,'#2#') from ( select VERSION, xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#') from ( select VERSION = @@version ) a ) a1 ) a2 ) a3 ) a4 ) a4 ) a5 go grant select on [dbo].[V_SQL_SERVER_VERSION] to public go
