Hi,
The subject say it all :)
I have a field which stores date which the datetime datatype... The problem is that I am also getting time information in this stored field.
I just want to extract the date part in dd-Mmm-yy format (e.g. 07-May-04). I tried convert (char (8), MyDateField, 112) but it gives me date in yyyymmdd format. How do I get the results in dd-Mmm-yy format instead?
Thanks.
Hi,I wanted to know how actually a database converter works.I am working on a converter from DBF to MS SQL server 2000using Visual Basic 6.0. I wanted to know that once a legacy databaseis enterd in the program, how does it get normalised.I have aboout 40 tables in DBASE IV format. I want to convert theminto relational database and store them on server. But on conversion,how can the converted data be normalised by itself.Awaiting the replies,Thanks
View 2 Replies View RelatedHello All,
I'm having a little problem in witting an SQL statement.
I'm sure there is a simple solution for it but its just not clicking with me!.
Anywayz,
I'm trying to write a statement that in the FROM clause, there is a formula, and i would like to assign conditions to this formula in the WHERE Clause
Ex:
SELECT DateValue(FLD_AS_STRING) FROM Table1 WHERE ???
I want the statement to convert the FLD_AS_STRING field (which is in string format) to Date format, and in the same time i want to only view fields that it's FLD_AS_STRING is greater than today's date (after it is converted to Date)?
How can i do that in this statement?
Thanks in advance,
Best Regards
Firas S Assaad
Hello All,
I'm having a little problem in witting an SQL statement.
I'm sure there is a simple solution for it but its just not clicking with me!.
Anywayz,
I'm trying to write a statement that in the FROM clause, there is a formula, and i would like to assign conditions to this formula in the WHERE Clause
Ex:
SELECT DateValue(FLD_AS_STRING) FROM Table1 WHERE ???
I want the statement to convert the FLD_AS_STRING field (which is in string format) to Date format, and in the same time i want to only view fields that it's FLD_AS_STRING is greater than today's date (after it is converted to Date)?
How can i do that in this statement?
Thanks in advance,
Best Regards
Firas S Assaad
I have a list of 20 different servers all are ACTIVE/ACTIVE clusters.
How I can find out the physical node and how to find out on what virtual node that physical node is sitting and what instances that physical node is running.
will appreciate help and email at cqldba@gmail.comt
thanks
ronny
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,
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
This pertains to SQL Server 2000Do others have the problems that we have in our company, where we havea field from a table, and need to know all the SQL Server processesthat may change that value? I'm still hoping somebody can tell me of atool, or set of queries or something that will document the fields inthe tables and the processes that affect them, along with the valuesthat it sets. For example:TableA.FieldA:sp_InsertNewRec: sets field to Null when new record createdsp_ChangeStatus: sets field to the value of the parameter passed intr_LogChanges: sets field to be the current datetimeMaybe we're just too sloppy at my company, but it seems to be fairlyobvious need to have some sort of automated process that can providethat documentation. With thousands of fields, hundreds of storedprocedures, triggers, DTS's etc..., documenting this information is aVERY daunting task. Any help at all would be appreciated.Gary
View 1 Replies View RelatedHello,
I understand that SQL Express is a free tool and its placed between Access and SQL 2000 / 2005.
I also understand that it supports Database upto 4 GB, which is quiet sufficient for any small or medium sized
company.
My question is a bit confusing... let me explain. We get Access database free by the hosting comany. We just have to
pay extra if we need SQL 2000 or SQL 2005 support, depending on size.
Will we get SQL Express Free support as its a lready free for all. In Access case, we just have to upload .mdb file along
with rest of our code, what is the procedure for uploading SQL EXPRESS data. I believe since its a light weight
tool,all the procedures must be same like Access.
Pl. guide with more details.
Dear GroupI'd be grateful if you can provide me with a hint for the following:Fields Table ContactContactIDFirstnameLastnameFields Table ContactMethodContactMethodIDContactMethodTxtContactMethodTypeIDLinked by Tables:Fields Table LkTbl_Contact_ContactMethodLkTblIDContactIDContactMethodTypeIDFields Table ContactMethodTypeContactMethodTypeIDContactMethodThe purpose of this construct is that I keep all contactmethods suchas Email, Mobile, Home Work, Web in table ContactMethods. Whether it'se.g. an Email or Mobile is identified through the ContactMethodType.That enables me that a Contact can have 3 Mobile Numbers, 2 Emailaddresses.For the sake of simplicity, lets say I want to return the Contact Nameand Home, Work and Mobile numbers. Not all of them but the firstmatching record of each from the ContactMethod table.I must note that the contents of table ContactMethodType for columnContactmethod looks like this:EmailMobileHomeWorkWebI do:Select Firstname, Lastname, Home, Work, Mobile FROM ContactJOIN LkTbl_Contact_ContactMethod ON(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)JOIN ContactMethod ON (ContactMethod.ContactMethodID =LkTbl_Contact_ContactMethodID)JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =ContactMethod.ContactMethodTypeID)The question is, how do I map the result from table ContactmethodTypeto my fields Home, Work and Mobile in the resultset? The Type isidentified in ContactMethodType but I can't figure out how to do thisbest.Your help and suggestions or any other better solution is veryappreciated.Thanks in advance for your help and efforts,Martin
View 3 Replies View RelatedDoes anyone know if there is a way to set a screensaver to auto log off users?
thanks for your time.
I absolutely agree with you.
I managed to find the error, it was a typo.
Here is the code, if somebody is interested about it. It is oracle, not SQL server, but the idea is the same.
drop view a;
drop view b;
drop view figure5_9;
create view a
as
select d.donorID, dt.donortype, dt.donortargetq1
from donortype dt, donor d
where d.donortype=dt.donortype;
select * from a;
create view b
as
select a.donortype, sum(c.ContributionAmount) as amount
from Contribution c, a
where c.ContributionDate between '01-JAN-06' and '31-MAR-06' and a.donorID=c.donorID
group by a.donortype;
select * from b;
create view figure5_9
as
select b.donortype, a.donortargetq1, b.amount, b.amount/a.donortargetq1 as percentage
from a, b
where a.donortype=b.donortype
group by b.donortype, a.donortargetq1, b.amount;
select * from figure5_9;
Thanks for the help anyway ;-)
Hi,
I am trying to extract data from a MySQL DB. I have installed the MySQL Connector (mysql-connector-odbc-3.51.23-win32), then set up an ODBC connector in the Control Panel > ODBC.
Then in BIDS I have setup a new ODBC connection in Connection Managers, and then used the ODBC connection I setup above so at that point everything looks cool.
Now what? Do I setup a Execute SQL Task or how do I get the data out and into a Data Flow Task?
Thanks
If you have followed a link to this page (i.e. the URL contains "SearchTerms=xxx") please scroll down to see items of interested highlighted
This list of SQL Server Frequently Asked Questions (FAQ) was originally compiled in August 2005 based on the most frequently "read" posts on SQL Team. Since then new links have been added as topics become popular - so it might be better referred to as FGA - Frequently Given Answers!
NC=Indicates that the Article has NOT been checked for relevance. As time goes on these are getting fewer and fewer
The latest change is in Red
Recent changes are in Teal
URL to link to this post: 17Nov2005
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=
Followed by either "Keyword1,Keyword2" or "PhraseWord1+PhraseWord2"
The icon allows you to RightClick-CopyShortcut to provide a link to this topic with appropriate keywords - I got bored of having to make them manually! 07Mar2006 Stats
Homework
I put this one at the top, in case you don't read any further. To find out if your homework will be answered on this forum you should see if it qualifies:
Requirements for homework questions 07Mar2006
Example of Failure to meet the minimum requirements 07Mar2006
Learning SQL www.sql-tutorial.net 07May2007
www.firstsql.com/tutor.htm 07May2007
www.w3schools.com/sql/default.asp 07May2007
Why?
Why is my LDF Log File so big / getting bigger / bigger than my MDF 14Oct2005
How to
How to ask a question on SQL Team and get a quick answer - i.e. the information you need to provide 06Nov2006
How to format your question or a reply (in particular see the [code] option 18Sep2007
how to encrypt and decrypt my password 07May2007
Rounding up/down to nearest multiple (e.g. order in dozens, buy at least 30) 28Sep2006
Replace multiple spaces, or other character, with single space 28Sep2006
Generate a Random String 13Sep2006
String comparison 24Jul2006
Article: Images and SQL Server
How to get Images or blobs into SQL table (NC)
Inserting Images as BLOB in SQL server table (NC)
How to use DeBUG window in QUERY Analyzer (NC)
Use a Parameter in the Top clause
Dynamic SQL or How do I SELECT TOP @var records?
Can't insert NULL into table with unique key (NC)
Efficient paging of recordsets with T-SQL
Article: Paging in SQL 2005 07May2007
Execute dynamic SQL that is longer than varchar(8000) 05Oct2005
Convert multiple spaces to single spaces 14Oct2005
Making Upper/Lower Case Sensitive comparison 27Oct2005
Convert to Proper Case (each word starts with capital) 19Feb2006
Deleting a massive number of rows 04Mar2006
Combine values from rows into a column 05Oct2005
i.e.
FOO 1
FOO 2
becomes:
FOO 1,2
CSV / Splitting delimited lists
WHERE IN @MyCSV
Article: Passing a CSV or Array to a Stored Procedure (NC)
Best split functions
and Concatenating data from rows with same GroupBy column
Triggers
Triggers to audit changes
trigger to keep tables matching
Set Update/Modified date column using a trigger 02Nov2005
Deactivating a trigger 07Feb2006
SQL Team Articles
Article: Identity and Primary Keys - and Surogate Keys See also
Article: Sending SMTP Mail using a Stored Procedure (NC)
Article: Dynamic ORDER BY (NC)
Article: More Trees & Hierarchies in SQL (NC)
Article: Using SELECT to INSERT records (NC)
Article: Deleting Duplicate Records
Article: Returning a Row Number in a Query (NC)
Article: Calculating Running Totals (NC)
Datatypes / Collation
GUIDs (NC)
IsGUID check GUID is valid 01Aug2007
DATALENGTH() and LEN() 21Sep2005
Compare BIT columns and a BitMask column 05Jan2006
Collation conflict in Assignment or WHERE clause 05Feb2006
(See "Developer / Techie stuff" below for some other Collation links)
Datatypes - Dates
Time Only Function: F_TIME_FROM_DATETIME 29Apr2006
Date/Time Info and Script Links (including UNIX time) 16Apr2006
Date formatting (
Format Date/Time (NC)
last date of the month (NC)
Getting the date portion of a datetime value or
Round a date to Year, Quarter, Month, Day, Hour or Minute
How to get date in t-sql (NC)
compare date with current date (NC)
convert varchar to datetime (NC)
Compare dates regardless of time? (NC)
Convert varchar to date (NC)
Time in Analog (NC)
Start of Week Function (NC)
Earlier date than 1/1/1753
Techniques
Prevent Administrators accessing a database 14Sep2006
SELECT vs SET 26Sep2005
What is a cursor, Why is it bad? 23Sep2006
Cursor or Not (NC)
TSQL/ SP Best Practices (NC)
case-sensitive (NC)
User-Defined string Functions Transact-SQL (NC)
Passing an array into a stored procedure (NC)
Comparing two tables or views easily (NC)
All Tables in All Databases (NC)
Better Phonetic Matching Algorithm (A better Soundex)
Levenshtein Edit Distance Algorithm (A better Soundex)
Fuzzy matching 01Aug2007
Find tables with no PK (NC)
Function: HTMLDecode (NC)
How to protect errorlogs to tables from rollbacks (NC)
Distance of Zip/Postcodes
Great Circle Formula 07May2007
Haversine Formula 07May2007
Accuracy v. Speed 07May2007 See also here, here, here, here and here and here
Upsert - combined Update/Insert Procedure 01Aug2007 and here
Display all columns but only for rows with the most recent date 01Aug2007
Developer / Techie stuff
Changing column names on referencing objects 07May2007
When was DB object last updated 07May2007
How to clear the whole database 07May2007
Move / Script Development Database Changes to Production Database / Ship Application updates 04Nov2006
Script to analyze table space usage 11Oct2006
What version, and Service Pack, of SQL Server am I using? 23May2006
SQL Beautifier 13Apr2006
Performance / System Monitor / PerfMon - preferred values for counters 04Apr2006
SQL Server 2000 Knowledge Base Articles - updated every 3 days 21Jan2006
Concerns about SQL 2000 SP4 08Jan2006
Support stops for SP3a AND installing sp4 on cluster 26May2006
How to read Mssql transaction log file
Compare data in two tables / two databases 31Oct2005
Recommended books 07Oct2005
Good tools for DBAs 06Oct2005
Best SQL Security Method (NC)
Some query optimalisation (NC)
Requirements for Horizontal Partitions
Multiple SQL Server 2000 Instances (NC)
Primary Key v. Unique Index (NC)
HOWTO Run SQL Profiler without sysadmin rights (NC)
upgrade from SQL7 to SQL2000 (NC)
xp_cmdshell execute right (NC)
Monitor object usage over time (NC)
size of user tables in a database (NC)
Changing Column Length (NC)
Add, Sub, Mul, Div With Really! Big Numbers (NC)
So you're a DBA eh?!
Why I'm not a DBA (NC)
Do you S-Q-L or Sequel (NC)
Interview questions (NC)
More Interview questions 11Sep2006
Best steps to becoming a DBA (NC)
Developer / Techie stuff - Collation
Bulk Delete of Records 07May2007 and locking issues 01Aug2007
Deleting lots of Records from a large table 01Aug2007
Synchronising data using staging tables 07May2007
Version Control 07May2007
how to change Collate on the server 20Sep2005
How to change default collate in SQL 20Sep2005
Cannot resolve collation conflict for equal to OP 20Sep2005
different Collation Settings between Databases 20Sep2005
Database Design and Structures
Codes - Common Lookup Tables CLT 04Jun2006
Good table naming convention 09May2006
Naming Store Procedures 09May2006
Recursion / Hierarchy 29Apr2006
Hierarchies (NC)
Normalisation (NC)
NULL opinion? (NC)
Finding evidence of sql injection attack
Security Testing: SQL Injection 09May2006
Locking
Using the NOLOCK hint 07Jun2006
how does row locking work (NC)
EMail
sp sending mail (NC)
CDOSYS Send Email (NC)
XML
SQL Server 2000 XML (NC)
Adding an XML Root Tag
Handling tricky namespaces in OpenXML
Backup / Restore / Replication / Jobs / Houskeeping
Backup
How Backup works
What type of backup should I use? - Full, Differential, Transaction? Should I shrink the log? 22Jan2006
Automating Backups - Scripting v. Maintenance Plan / Wizard 08Mar2006
Backup to Disk, or direct to Tape? 05Dec2006
Disaster Procedure Checklist I think I have a database corruption, how do I mitigate any further damage and get upright again? 07May2007
How I Rescue Data from Corrupted Databases 07May2007
Disaster Recovery Plan 07May2007
Quiesce the SQL Service to enable low-level copy 01Aug2007
Restore
RESTORE syntax / example
Restore SQL 2005 to SQL 2000 via Downgrade 07May2007
Restore Full and all TLogs backups based on files in disk folder 07Dec2006
Move database to a new server (with minimal downtime) 16Feb2006
Moving to a new sql server 25Jun2006
Fix Orphaned Users (after a Restore or moving DB to a different server)
Script User Logins after a Restore 01Aug2007
and
Scripting users & logins from one server to another (e.g. after moving DB to a different server) 25Jul2006
how long will take to restore a 10 gb?
All I have is the .bak file
Restoring .BAK file to a different server?
Unable to restore/attach a MDF file (single file attach / sp_attach_single_file_db)
See also: This and this and this
Shrink Database / Files
Shrinking TEMPDB 19Apr2006
Database Shrinking 09Jan2006
Log Shipping -Shrinking Log and Data files (NC)
MSDB Massive msdbdata.mdf
Housekeeping / Maintenance
Tara Blog Houskeeping Routines 06Oct2006
Index Tuning Wizard 07May2007
User Rights for SQL Service 07May2007
When was SQL Server last restarted? 07May2007
Optimising your indexes with DBCC INDEXDEFRAG 06Oct2006
Detach Move Db & Log File Reattach 17Mar2006
Rebuilding Indexes
DBCC CHECDB, CHECKTABLE, NEWALLOC (old)
Suspect Databases after Power Cut?
Jobs failing (NC)
Reindex All Tables in All Databases (old)
deleting replication (NC)
Export / Import / DTS
Copying/backing up DTS Packages (NC)
Export to Excel (NC)
Generate INSERT statements for N rows of data
Tool to script database records? (i.e. generate INSERT statement script)
How to convert DATE Format from DD/MM/YY to MM/DD/YY in DTS (NC)
Importing an Excel File with DTS (NC)
Is DTS Secure for credit card backup to Access? (NC)
Winzip in scheduled DTS Package (NC)
Article: Using BULK INSERT to Load a Text File (NC)
Output / Reporting / Formatting
Pivot table (NC)
Article: Dynamic Cross-Tabs/Pivot Tables (NC)
which led to:
More Dynamic CrossTabs using T-SQL 04Aug2006 (Jeffs Blog)
Error Messages
SQL Server does not exist or access denied (NC)
Connections
Linked Server Newbie Question (NC)
Configuration / Hardware
/3GB in boot.ini on Win2003 standard 19Oct2005
SQL memory /3gb /PAE AWE 19Sep2005
SQL Server Standard & 4GB memory
Using AWE option in SQL
Increasing SQL Server Memory
MSDE
MSDE Setup fails (NC)
MSDE Install Issue (NC)
Installing MSDE on XP Pro (NC)
Backing up MSDE database (NC)
SQL 2005 / Yukon
Migrating to SQL 2005 Hints and Tips 01Aug2007
SELECT not allowed in Yukon/SQL2005? (NC)
Download Yukon/SQL2005 (NC)
SQL Server Management Studio (NC)
MySQL, Oracle, and the others
MySQL not as good as MS SQL?
Migrating from Oracle to SQL Server
Other
SQL Reporting Service SP2? (NC)
Got an hour .. or ten ... to spare?
Would you work at Elwoo's office?
Simple Quiz
What is a Yak? 07May2007
Hello, I have several SQL 2000 logins on our servers. I removed the builtin/ groups and only have SQL authentication users, although the server is operating in "Mixed Mode". How can I secure this? I don't understand how we can just connect to any DB without ever being asked to log in. Let me know if you can help. Thanks.
Here is my goal please let me know if it is possible.
I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.
Does CE
Support transactionscope?
What would I use to create tables and sp's for my sdf database? Server Managment Studio or something else?
Thanks
Hi Everyone,
I am new to the forum and would like to have a question answered. I want to to install Sql Server Express with advanced services but also want to have IIS installed as well. My question is, how do I install IIS without it opening Port 45? When I installed it, it would not let me finish installation without letting it act as a server. Is there any work around to solve this problem that any of you have managed to use?
I'm in China doing TechEds in Shanghai, Guangzhou, Beijing and Hong Kong - returning to the office Monday 2nd October.
There may be significant delays in my replies to any topics on this forum. Just fyi so you don't sit waiting for me to respond.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
It's been pretty quiet here recently - hopefully people either aren't experiencing corruptions or they're finding what they need in the previous threads.
Just a quick note to say that I'll be totally offline from tomorrow until January - going diving in Indonesia (http://www.wakatobi.com).
Happy holidays and may your pagers be silent throughout
Cheers
Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Plz helpHow to find my previous topic??CheersDishan
View 1 Replies View RelatedI'm thinking of doing some basic parsing of address. I want to seprateout the house number from the street name from the suffix. I have thesuffix's from the USPS, but I cant find a database of US street names.Anyone come across one or know where I can get one?TIARob/end off topic
View 2 Replies View RelatedHi all!
Is there a possibility to call (and receive records) SQL Server (or other databases) procedure from MS Access?
Greetings,
I have a question about the best method to ensure the proper flow of control within a package. I have three related SQL tasks €“ A, B, and C €“ that must execute in sequence. If any one of the tasks fails then the remaining tasks should not execute because each is dependent on the previous step. (A €œsuccess€? precendence constraint.)
Likewise, I have a second set of SQL tasks €“ 1, 2, and 3 €“ that must execute in sequence. Task 3 should never execute unless tasks 1 and 2 have been successful.
I consider these to be two independent logic paths in the same package and, indeed, when I run the package both task A and task 1 begin running at the same time.
What I€™m not sure of is how to add a new task €“ Z €“ that will execute only after both the A-B-C and 1-2-3 task paths have completed. Diagramatically, this would be like having a €œcompletion€? constraint linking task C to task Z and another €œcompletion €œ constraint linking task 3 to task Z. In other words, the two independent paths merge at task Z. Task Z is an audit step that must always run €“ it emails summaries of the database activities performed by the other tasks.
What has me puzzled is how I get to Z when there is a failure in either the A-B-C path or the 1-2-3 path. As things stand now, the package will never reach task Z if there is a failure in an earlier task because they are joined by a €œsuccess€? constraint.
I thought I could get around this by using a €œsequence container€? but I discovered when I added A, B, and C to a container that I couldn€™t use the €œsuccess€? constraint I needed. I was just guessing that this might be the way to do it.
Can anyone help me with a best practice approach to the control flow issue I face? I€™m new if that isn€™t apparent by my question, so don€™t be afraid to make your answer simple and plain.
Thanks for any help,
BCB
I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'
ALTER PROCEDURE [domainnamemyaccountname].[Categories_I]
AS
BEGIN
/* delete contents from Category table and reseed
Cannot use truncate on a table which contains constraints therefore
use DBCC to reset the seed and DELETE to clear the records
*/
DELETE dbo.Category
DBCC CHECKIDENT ('Category', RESEED, 0)
-- Now, insert the initial 'All' Root Record
ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1
PRINT 'Dropped FK'
SET IDENTITY_INSERT Category ON
INSERT INTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
PRINT 'Inserted All Record'
INSERT INTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
PRINT 'Inserted Store Record'
SET IDENTITY_INSERT Category OFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO dbo.Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
PRINT 'Inserted Rest of Category Records'
PRINT 're-create FK Call'
ALTER TABLE dbo.Category
ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY
(
ParentCategoryID
) REFERENCES Category (
CategoryID
)
PRINT 'create FK2'
END
Other errors:
(1 row(s) affected)
Checking identity information: current identity value '2', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3728, Level 16, State 1, Line 6
'Category_Category_FK1' is not a constraint.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors.
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.
Schemas & Data:
http://www.webfound.net/category.bmp
http://www.webfound.net/category.sql
http://www.webfound.net/categoriesstaging.sql
http://www.webfound.net/stagingdata.txt
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
View 9 Replies View Relatedhi, when i use the getdate() function, i will get '4/14/2004 AM 11:25:56'. how can i edit the getdate() function to get just the date only '4/14/2004'.
thanks in advance
I can get the date to list all of the dates in the recordset, but when I use request.querystring("date") to specify all the recordsets with the date "2/26/2005" for example, the recordset comes up empty. Here is my sql statement:
SELECT eventdate=convert(varchar, eventdate, 101), eventtext, eventtime, location, eventtitle, eventID, picture
FROM dbo.calendar
WHERE eventdate like 'thee' and status='live'
ORDER BY eventdate ASC
this will return everything when the browser url shows this: http://www.kapsi-western.org/calendar.asp?date= However, if we put a date in there, and made it like this http://www.kapsi-western.org/calendar.asp?date=2/26/2005, then nothing comes up even though there is event with that date.
I'm trying to find the first Monday for any given year. I've found something
that works, but I typically find the most convoluted way to do a simple task.
Is there something simpler/cleaner than this?
DECLARE @intYear INTEGER
DECLARE @dtmFirstMon DATETIME
DECLARE @dtmWorkDate DATETIME
SET @intYear=YEAR(GETDATE())+2
SET @dtmWorkDate=CAST('01/01/' +CAST(@intYear AS CHAR(4)) AS DATETIME)
SET @dtmFirstMon=(SELECT CASE
WHEN DATEPART(dw, @dtmWorkDate)=2 THEN @dtmWorkDate
WHEN DATEPART(dw, DATEADD(D,1,@dtmWorkDate))=2 THEN DATEADD(D,1,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,2,@dtmWorkDate))=2 THEN DATEADD(D,2,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,3,@dtmWorkDate))=2 THEN DATEADD(D,3,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,4,@dtmWorkDate))=2 THEN DATEADD(D,4,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,5,@dtmWorkDate))=2 THEN DATEADD(D,5,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,6,@dtmWorkDate))=2 THEN DATEADD(D,6,@dtmWorkDate)
END)
PRINT(@DTMFIRSTMON)
I want today's date (GetDate()), but I want the hour, minutes, et cetera set to 0 as they play havoc with my date comparisons.
How do I do it?
Thanks.
Hi All,
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Many thanks in advance!!
Brad
----------------------------------------------
declare @today DateTime
Select @today = GetDate()
print @today
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
View 1 Replies View RelatedI simply want to format a datetime to be printed out as 'mm/dd/yyyy', instead of 'January xx, xxxx tttt'. For example:
declare @thedate as smalldatetime
set @thedate = (SELECT SampDate
FROM table where tableID = 1)
print @thedate
This has got to be ridiculously obvious. Right??
Hello Friends ,
As Iam a very beginner to this sqlserver arena , Iam getting more and more doubts. Can anyone of you help to display a date as string type which is of format, "date/month/year hour:month:second:millisecond".
Please get rid of the huge command which Iam using now,
select rtrim(cast(datename(day ,getdate()) as char )) + '/'+ rtrim(cast(datename(month ,getdate()) as char )) + '/' + rtrim(cast(datename(year ,getdate()) as char )) + ' ' + rtrim(cast(datename(hour ,getdate()) as char )) + ':' + rtrim(cast(datename(minute ,getdate()) as char )) + ':' + rtrim(cast(datename(second ,getdate()) as char )) + ':'+rtrim(cast(datename(millisecond ,getdate()) as char )) as date