How to change mm/dd/yyyy to desired format
hi guys, I have date in format mm/dd/yy.For eg:4/1/2009 means April 1 2009.I want to get format as April 1,2009 in sql.Can anybody help? ...
sql
What to do when I want to use database constraints but only mark as deleted instead of deleting?
I am working in a project where database items are not deleted, but only marked as deleted. Something like this: id name deleted --- ------- -------- 1 Thingy1 0 2 Thingy2 0 3 Thingy3 0 I would like to be able to define something like a UNIQUE constraint on the name column. Seems easy, right? Let's imagine a scena...
Validate if a column has a null value
Which SQL would be faster to validate if a particular column has a null value or not, why? 1) SELECT * FROM TABLE1 WHERE COL1 IS NULL Execute this query and then check if you are able to read any records. If yes there are null values. 2) SELECT COUNT(COL1) FROM TABLE1 WHERE COL1 IS NULL Read the count which is returned to determine i...
Using Autonumbering in Access - INSERT statements
I'm having trouble running an INSERT statement where there's an autonumber as the PK field. I have an Auto-incrementing long as the Primary Key, and then 4 fields of type double; and yet Access (using ADO) seems to want five values for the insert statement. INSERT INTO [MY_TABLE] VALUES (1.0, 2.0, 3.0, 4.0); >> Error: Number of query va...
Cannot access WSS 3.0 sites after movind Content and Config DBs
Problem: No access to internal sites after new Instance of SQL was installed on MS SQL Server Description: I've got a WSS 3.0 installed on the W2K3 Sever. Originally, there was only one Instance of MS SQL (MSDE) for Backupexec (bkupexec). Later, we installed WSS 3.0 and instead of installing the new instance of SQL we installed on the...
Insert query in Access
I have query build from Visual Studio 2005. I am inserting records from SQL database to Access. My query is insert into i_mails (id,from_mails,to_mails,cc_mails,subject,body, anchor_level_id,attachment,forward_status ,reply_status,delete_status, read_status, received_date,response_date, batch,forward_score,delete_s...
What is the difference between function and procedure in PL/SQL?
Duplicate: what is the difference between a function and a procedure What is the difference between function and procedure in PL/SQL ? ...
inconsistent display of utf8 accents
I have one database, with one table, with a particular field which has descriptions of various clothing. These descriptions often contain umlauts or similar characters. I am retrieving this fields from two different php fields, and I am not doing anything to the data, yet it displays inconsistently. In file1, it will display correctly ...
"Conditional Conditions" in a WHERE clause (Which condition to apply depends on a "mode" flag)
I have a situation which I have solved in two different ways, but was wondering what people thought about the options, and if they have any other alternatives... The system is processing "intervals" of data. All the data is allocated to an "interval" The interval is represented by an "interval_start" DATETIME in the Fact table A...
Add a sequential number on create / insert - Teradata
In oracle we would use rownum on the select as we created this table. Now in teradata, I can't seem to get it to work. There isn't a column that I can sort on and have unique values (lots of duplication) unless I use 3 columns together. The old way would be something like, create table temp1 as select rownum as insert_num, ...
What is the smallest way to store a UUID that is human readable?
What is the smallest way to store a UUID that is human readable and widely database compatible? I am thinking a char array of some sort using hex values? ...
T-SQL search html with regex?
In my database I have a field wich contains a html document. Now there must be a possibility to search in this document. However, the html tags may not be found. So when I have something like this: <html> <head> <title>Bar</title> </head> <body> <p> this content my be found </p> </body> </html> It is possible th...
Case Statement in Access
Can you use case statements in Access, im trying to det the max date form 2 columns but keep getting syntax errors in the following code. CASE WHEN dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date] THEN dbo_tbl_property.LASTSERVICEDATE ELSE Contour_dates.[Last CP12 Date] END AS MaxDate ...
SQL: finding double entries without losing the ID
I have 1 table "Products" that looks like this: ID Product Shop Color 01 Car A Black 02 Car B Black 03 Bike C Red 04 Plane A Silver 05 Car C Black 06 Bike A Red In this example, a Product always has the same color, independent ...
SQL query that distinguishes between ß and ss
It seems that ß=ss in SQL. I need to be able to distinguish on a strict charcter equivalent basis. i.e. select * from table where name like '%ß%' yields Brian Bruß Steven Sossmix etc.. I've looked at different Collations to see if there is one that ignores character expansions, but no luck so far. Database has Latin1_General_CI_AS as...
Sane/fast method to pass variable parameter lists to SqlServer2008 stored procedure
A fairly comprehensive query of the brain has turned up a thousand and one ways to pass variable length parameter lists that involve such methods as: CLR based methods for parsing strings to lists of integers Table valued functions that require the presence of a 'Numbers' table (wtf?) Passing the data as XML Our requirements are to p...
Storing UUID as base64 String
I have been experimenting with using UUIDs as database keys. I want to take up the least amount of bytes as possible, while still keeping the UUID representation human readable. I think that I have gotten it down to 22 bytes using base64 and removing some trailing "==" that seem to be unnecessary to store for my purposes. Are there an...
How can I transform this Lambda Expression into SQL statement?
Hi everyone!! I have a lambda expression that has this: Convert.ToDateTime(a.startTime).TimeOfDay >= Convert.ToDateTime(startTime).TimeOfDay But, I have to create a procedure in SQL Server and how should be the statement above to SQL statement? I've tried to use some kinda 'convert(startime, getdate(),8) but it didn't work. And I f...
How can I best extract transitions in a transactional table?
Hypothetical example: I have an SQL table that contains a billion or so transactions: | Cost | DateTime | | 1.00 | 2009-01-02 | | 2.00 | 2009-01-03 | | 2.00 | 2009-01-04 | | 3.00 | 2009-01-05 | | 1.00 | 2009-01-06 | ... What I want is to pair down the data so that I only see the cost transitions: | Cost | DateTime | | 1.00 | 2...
Import XML to SQL using C#
I know its kind non sportiness asking for this kind of help, But I am relay stacked on this for while, Temporally I am reading two C# books and working everyday over 9 hours. Okay here is my problem I have WIN C# application that I almost done. In SQL i got a three tables look like this: CREATE TABLE [dbo].[Racuni]( [BROJ] [varchar](1...