Hello all
Someone gave me this:
Month Day Year Hour Min Sec
9 9 2006 15 9 36
And I need to make a descent date format out of it which looks like this:
Sun Sept 09 15:09:36 CEST 2006
I can concatenate the whole thing but I'm stuck with the "sunday" part.
I'm sure there are some experst out there who know how to do this is in 1 minute;)
Regards
Worf
Hello,
I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:
Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04
The results I am looking for would be as follows:
Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04
I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??
Thanks in advance!!
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
I hate manipulating dates in SQL. One of the many things Access and VB handles much better!
I wanted a function like DateSerial(Year,Month,Day) so I created one.
One caveat: The Year must be >= 1800. But all other numbers can be most any int value that results in a valid date.
Which means you can do:
MDate(Year(@d),1,1) (first day of date @d)
MDate(Year(@d), Month(@d) + 1, -1) (last day of month of date @d)
MDate(2000,1,1) (create a date quickly and easily w/o convert)
MDate(Year(@d)+1,1,1) (get first day of next year for date @d)
..etc... whatever you can do with VB's DateSerial() function, except for the year must be >=1800.
Or, does this exist already in SQL and I'm missing it somewhere??
Is there an easier/better way to do this?
* * * *
Create function MDate(@Year int, @Month int, @Day int)
returns datetime
AS
BEGIN
declare @d datetime;
set @d = dateadd(year,(@Year - 1800),'1/1/1800');
set @d = dateadd(month,@Month - 1,@d);
return dateadd(day,@Day - 1,@d)
END
- Jeff
Edited by - jsmith8858 on 12/12/2002 14:04:03
First, let me say I'm really new (and probably dangerous) with SQL.
I want to take a column in my database called "publish_up" and add 4 days to the date and put it in a column in the database called "publish_down." The publish_up date is the date to start publishing and the publish_down is the date to stop publishing. A Joomla! mambot will then come by and archive the items with a publish_down date that matches today's date.
I'm pretty sure my server has SQL 5 if that's an issue.
Thanks for any advice or help you can offer.
hi,I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.Thanks very much.
View 2 Replies View Relatedselect no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 1 Replies View Relatedselect no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 11 Replies View RelatedHi,
I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).
Regards,
Simon.
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)
INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL
[Code] ....
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedI have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
Hi everyone.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
iif(somevalue is nothing, 'Other', somevalue)
Thank you in advance.
I am trying to develop a web site. I have a local ms sql database on my machine.
I am trying to connect to a ms Sql database on a goDaddy server from the application.
I am trying to understand the connection string and its total properties.
here is what I think should be in my web.config file
<
add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;
Database=DB_XX10;
User ID=myID;
Password=myypassword;
Trusted_Connection=False" providerName="System.Data.SqlClient"
/>
<remove name="LocalSqlServer"/>
can someone please tell me where I am going wrong, Thanks for your help.....
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
so like
InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1)
<asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
I don't think that is gonna work, so can you please help me make that code work?
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.
I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.
My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.
I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
Hey guys, don't know if this is in the right place but here goes.
I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists.
So I created a Sqldatasource1,2,3 and had to create the connection strings for each one.
ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies View RelatedHi,
Im struggling to work out the connection string to my ms sql database.
My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string?
Thanks, Mark Lawton
I've got a GridView, a datasource, 2 textboxes and a button. If I choose to bind the GridView to the datasource the correct data is shown. But if I modify the datasource and chooses the TextBoxes as Parameter Controls it doesn't work out as I expected.
Can I please have a step-by-step response which will work? And expecially if one if the TextBoxes eguals nothing/null. Because this is very important, too, and that part doesn't work either.
This seems so simple but I can't find any examples to do it..
I have the following in a text file "Lastname, Firstname MI" and I want to select each part separetly.. What would be the SELECT statement to get the Lastname up to the comma, then the Firstname after the comma, then the MI after the last space..
Thanks for the help, and any reference to learn more.
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like:
User Answer
1 Good,Fair,Good,Bad
2 Bad,Good,Good,Good
3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
In MySQL I can represent a string as the following:
X'6162636465'
However, I can't seem to find an equivalent that is supported MS SQL from the last hour and a half of searching the net. Does anyone know of any way to do this?
I have a column called professional_degrees in my table
and it has the following data
professional_degrees
bs,ms,mba
bs,mba,
bs,ms,bs,ms
mba,mba
bs,mba
In the above data u can see some of the degrees are repeated.So how can i find them and delete them from the table.
Thanks.
I have a small problem that I can't figure out. Here's my table:
----------------------------------------
Members
----------------------------------------
ID | Name | Image
----------------------------------------
When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.
What I want to do is return the path of where the image is in the folders. Basically...
If Image is not null
SELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePath
If Image is null
SELECT 'livefiles/MembersDefault/1/image.jpg'
I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...
COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePath
but that gave me an error saying:
Conversion failed when converting the varchar value 'livefiles/Members/' to data type int.
I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL.
I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet.
What I have done is this.
I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server.
On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[stp_SecurityAuditReport]
AS
TRUNCATE TABLE SecurityEvents_Tmp
-- Parse Bank Number & UserName
INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data)
SELECT
DepartmentNumber = '001',
UserName = CASE
WHEN Strings LIKE '[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN Strings LIKE '-|[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked. |%'
THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
END,
Events.*
FROM Events
JOIN EventsToLog on Events.EventID = EventsToLog.EventID
WHERE SID NOT LIKE 'S-%'
-- Update blank usernames
UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME'
WHERE UserName = '' OR UserName = '-'
-- Update DepartmentNumbers with zeros
UPDATE SecurityEvents_Tmp SET
DepartmentNumber = CASE
WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR
UserName LIKE '[0-9][0-9][0-9]#%' OR
UserName LIKE '[0-9][0-9][0-9]$%'
THEN SUBSTRING(UserName,1,3)
ELSE '001' END
As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports.
I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!)
DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated
DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated
SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL
THEN 'All Department' ELSE b.DepartmentName END,
a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
FROM SecurityEvents_Tmp a
LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber
LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber
JOIN EventsToLog d on a.EventID = d.EventID
WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist)
AND a.TimeGenerated BETWEEN @TimeGenerated AND @TimeGeneratedEnd
ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated
This combination of utils and scripts does very good for producing generic security reports for branch officers.
But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from.
This is the Strings field from a failed logon (529)
200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397
And this is from Event ID 642 which was an account being created.
-|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|-
Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account.
So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string.
I could then use a case to get the information needed.
HELP PLEASE!!
Thank You,
John Fuhrman
http://www.titangs.com
Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making.
(This works on my test SQL2005 server)
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[BankNumber],
[UserName],
[DomainName],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data]
)
Select
[BankNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[SecurityEvents].*
FROM [SecurityEvents]
JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID]
WHERE [SID] NOT LIKE 'S-%'
Query Output:
quote:
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'Strings'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'Strings'.
Thank You,
John Fuhrman
http://www.titangs.com
I need to convert string data so that I can use it in a view.
example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters
Jeff
Guys,
I have following table where I need to concatenate varchar column.
For example
IDCOMMENT
__________________
1JOHN SMITH
1 SURRENDER
1TO COPS
I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
I find myself between rock and hard place.
My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5
The source table contains all six fields.
If I simply map the input to output columns, I get all fields output in each record, which is not what I want.
So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.
The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.
Is there any way to string these fields together while maintaining the original field length?
I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ