|
|
update GL set GL_DATE=convert(date,getdate())
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Still getting error 'Conversion failed when converting date and/or time from character string.' while converting by using your query.
|
|
|
|
|
Which means one of the strings in the source column is not a valid datetime value.
Which is why you should never store dates as strings.
If you're using SQL 2012 or later, you could use TRY_CONVERT[^] or TRY_PARSE[^], which will return NULL for any values it can't convert. Otherwise, you're stuck with converting the values manually.
NB: In your example, you should update the new GL_DATE1 column directly, rather than updating the original GL_DATE column and then trying to copy it across.
You'll probably also want to use the newer datetime2 type[^], which has a better range than the old datetime type.
alter table GL add GL_DATE1 datetime2(0) null;
update GL set GL_DATE1 = TRY_CONVERT(datetime2(0), getdate(), 104);
select GL_DATE from GL where GL_DATE1 Is Null And GL_DATE Is Not Null;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
modified 1-Feb-17 7:58am.
|
|
|
|
|
Got an exception 'datetime2' is not a recognized built-in function name' while running the query as you mentioned.
|
|
|
|
|
So you're using SQL 2005 or earlier? You'll have to stick with datetime in that case.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I need to know which is better for work and performance please :
I have to do many operation on database so I am hesitating between :
- create object "SqlConnection" and let connection opened during service (webservice) works
- using the instruction below every time I want to do a modification in dtatabase
using (SqlConnection sqlconnection = GetSqlConnection(connectionString, false))
{
sqlconnection.Open();
.....
}
Where
public static SqlConnection GetSqlConnection(string connectionString, bool disablePooling = true, bool forceMasterDB = false)
{
if (disablePooling || forceMasterDB)
{
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(connectionString);
if (disablePooling)
sqlConnBuilder.Pooling = false;
if (forceMasterDB)
sqlConnBuilder.InitialCatalog = "master";
connectionString = sqlConnBuilder.ConnectionString;
}
return new SqlConnection(connectionString);
}
Thank you
|
|
|
|
|
If you have the code, why not write a simple app to process a number of statements and check the execution time.
Ensure the statements executed are the same for each.
|
|
|
|
|
thank you for response but as I said I know what you suggest but I ask what is more safe also for me.
|
|
|
|
|
Create the connection as late as possible, and wrap it in a using block to ensure that it's always disposed of properly.
Don't disable connection pooling, since the underlying network connections can be expensive to set up and tear down. The connection pool is there to improve the performance of your code!
Trying to share a single connection object across multiple threads will only lead to errors. To avoid the errors, you'd have to synchronise access to the shared connection, which will significantly degrade the performance of your code.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thank you for response
Yes what I did is that every time I need the connection I use it in using block.
Also I use pooling always because I'm using the same connection string.
so you see this safe ?
|
|
|
|
|
Yes, it sounds like you're doing the right thing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Hi,
I need regular expressions in SQL Server for two things
- Get the first 3 characters from a string, no matter alphabets or numerical values but not special characters, like for example if I have file name A00Temp then it should give me A00 and if the string is _A00Temp then still it should give me the A00 only but not _A0, if it has ABCTemp then it should give me ABC
- the 2nd need is if I have a string with alphabets, numerical and special characters then it should give me only alphabets from that string
for exmple if I have: Abc def 230 then I should get abcdef, if I the string is _abc$#def then I should get only abcdef.
If I can do this using regular expressions, please help me, any type of help is very welcome, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
|
How i Access Other sql database in oracle sql app , database is open , I want that data for creating reports.
its my client database ,
|
|
|
|
|
This is not very clear.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
SQL 2012:
I have two tables, AttemptedRuns and Bays
This
SELECT *
FROM NexGen.dbo.AttemptedRuns
WHERE BaySerialNumber = '1545300531'
Produces 317 rows. The table has a column called 'BaySerialNumber'
So I want to join on bays so I can retrieve the BayLocation:
SELECT ar.Id as AttemptedRunId,
ar.AccessionId,
ar.StartTime,
ar.BaySerialNumber,
ar.CartridgeId,
ar.Result as AttemptedRunResult,
b.Location as BayLocation
FROM NexGen.dbo.AttemptedRuns ar
LEFT JOIN NexGen.dbo.Bays b on b.SerialNumber = ar.BaySerialNumber
WHERE ar.BaySerialNumber = '1545300531'
The problem is that I now get 1902 rows. It's duplicating the AttemptedRuns rows. See here
What I am looking for is to get the BayLocation from the Bays table for each AttemptedRow row.
What am I doing wrong??
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
There are more matches than 1 to 1. If you change it to SELECT * you'll see all the fields in both tables and can then see why you are getting more than one match.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
OK, but what I really want is to just get the BayLocation for each AttemptedRun record.
Is this possible?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Kevin Marois wrote: the BayLocation for each AttemptedRun record. I believe that is what you have originally:
SELECT ar.Id as AttemptedRunId,
ar.AccessionId,
ar.StartTime,
ar.BaySerialNumber,
ar.CartridgeId,
ar.Result as AttemptedRunResult,
b.Location as BayLocation
FROM AttemptedRuns ar
LEFT JOIN Bays b on b.SerialNumber = ar.BaySerialNumber
WHERE ar.BaySerialNumber = '1545300531'
This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
RyanDev wrote: This says to get all attempted runs and if there is a bay that matches on BaySerialNumber to get its information as well.
Correct, What I'm looking for is the APPEND the BayLocation to the end of each row of AttemptedRun data
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
But you have 6 matching rows in Bays. BaySerialNumber is not unique. That is why you need to run SELECT * and see why you are getting extra. Perhaps there is another field, like status, that you need to take into account. We can't know because we can't see all the data.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Well you are right. Turns out there are 6 rows in Bays with the same serial number. I was operating under the assumption that the serial number was unique.
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|