Hi,
I would like to convert real data type to datetime type. Example:
I have a real data type which is: 23,613456 (23 hours and 0,613456). I
would like to have it in hh:mm:ss format. How to do this? Can I use
convert/cas function?
Thanks for help
Rgds
Mario
I have the following SQL:
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
(
@StudioINT = NULL,
@Br1INT = NULL,
@Br2INT = NULL,
@Br3INT = NULL,
@Br4INT = NULL,
@OverBr4INT = NULL,
@CondoINT = NULL,
@ListingTypevarchar(10) = NULL,
@WindowAirINT = NULL,
@CentralACINT = NULL,
@BalconyDeckPatioINT = NULL,
@UseOfYardINT = NULL,
@DishwasherINT = NULL,
@WasherDryerINT = NULL,
@FireplaceINT = NULL,
@EIKINT = NULL,
@HardwoodFloorsINT = NULL,
@BroadbandNetINT = NULL,
@TVINT = NULL,
@ThermostatINT = NULL,
@LandlordNotPresentINT = NULL,
@SmokingINT = NULL,
@NoPetsAllowedINT = NULL,
@CatINT = NULL,
@MoreCatsINT = NULL,
@SmallDogINT = NULL,
@LargeDogsINT = NULL,
@DoorpersonINT = NULL,
@IngroundPoolINT = NULL,
@AboveGroundPoolINT = NULL,
@ElevatorINT = NULL,
@UseOfGarageINT = NULL,
@LaundryFacilitiesINT = NULL,
@HealthCenterINT = NULL,
@StorageAreasINT = NULL,
@WheelchairAccessINT = NULL,
@BusinessCentersINT = NULL,
@RentChargeMinINT = NULL,
@RentChargeMaxINT = NULL,
@DebugBIT = 1
)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
SELECT
r.REListingID,
r.REListingDate,
r.Username,
r.ZipCode,
r.ListingType,
r.StudioFlag,
r.BRFlag1,
r.BRFlag2,
r.BRFlag3,
r.BRFlag4,
r.OverBRFlag4,
r.CondoFlag,
a.WindowAir,
a.CentralAir,
a.BalconyDeckPatio,
a.UseOfYard,
a.Dishwasher,
a.WasherDryer,
a.Fireplace,
a.EIK,
a.HardwoodFloors,
a.BroadbandNet,
a.TV,
a.Thermostat,
a.LandlordNotPresent,
a.Smoking,
a.NoPetsAllowed,
a.Cat,
a.MoreCats,
a.SmallDog,
a.LargeDogs,
a.Doorperson,
a.IngroundPool,
a.AboveGroundPool,
a.Elevator,
a.UseOfGarage,
a.LaundryFacilities,
a.HealthCenter,
a.StorageAreas,
a.WheelchairAccess,
a.BusinessCenters,
a.RentCharge,
a.RentFrequency
FROMdb_REListings as r
INNER JOINdb_RentalAmenities AS a ON a.REListingID = r.REListingID
WHERE1 = 1
'
IF @Studio IS NOT NULL
SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio)
IF @Br1 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1)
IF @Br2 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2)
IF @Br3 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3)
IF @Br4 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4)
IF @OverBr4 IS NOT NULL
SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4)
IF @Condo IS NOT NULL
SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo)
IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType)
IF @WindowAir IS NOT NULL
SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir)
IF @CentralAC IS NOT NULL
SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC)
IF @BalconyDeckPatio IS NOT NULL
SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio)
IF @UseOfYard IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard)
IF @Dishwasher IS NOT NULL
SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher)
IF @WasherDryer IS NOT NULL
SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer)
IF @Fireplace IS NOT NULL
SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace)
IF @EIK IS NOT NULL
SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK)
IF @HardwoodFloors IS NOT NULL
SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors)
IF @BroadBandNet IS NOT NULL
SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet)
IF @TV IS NOT NULL
SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV)
IF @Thermostat IS NOT NULL
SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat)
IF @LandlordNotPresent IS NOT NULL
SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent)
IF @Smoking IS NOT NULL
SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking)
IF @NoPetsAllowed IS NOT NULL
SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed)
IF @Cat IS NOT NULL
SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat)
IF @MoreCats IS NOT NULL
SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats)
IF @SmallDog IS NOT NULL
SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog)
IF @LargeDogs IS NOT NULL
SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs)
IF @Doorperson IS NOT NULL
SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson)
IF @IngroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool)
IF @AboveGroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool)
IF @Elevator IS NOT NULL
SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator)
IF @UseOfGarage IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage)
IF @LaundryFacilities IS NOT NULL
SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities)
IF @HealthCenter IS NOT NULL
SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter)
IF @StorageAreas IS NOT NULL
SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas)
IF @WheelchairAccess IS NOT NULL
SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess)
IF @BusinessCenters IS NOT NULL
SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL
SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin)
IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @Debug = 1
PRINT @SQL
EXEC (@SQL)
Hi. I am trying to setup php4 to talk to MSSQL 7, but am having no luck. Both are on a Win2k server. I get the error below. Please can someone tell me what to do, or even a step by step guide to setting up php to connect to a MSSQL7 database. I originally created the database in access 2000 and now want to use MSSQL to use it. This is the error i get:
Warning: MS SQL message: Could not locate entry in sysdatabases for database 'customers'. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:Websiteewsiteestdb.php on line 13
Warning: MS SQL: Unable to select database: customers in C:Websiteewsiteestdb.php on line 13
Database unavailable
I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.
--Buddy
Hi,
I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.
The database forms the basis for a diary/calendar system for part-time employees and has two tables:
- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.
- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.
I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.
Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.
Any ideas?
Timings... sometimes there are almost too many ways to do the same thing.The only significant findings I see from all the below timings is:1) Integer math is generally fastest, naturally. Bigint math isn't muchslower, for integers that all fit within an integer.2) Converting float to varchar is relatively slow, and should be avoided ifpossible. Converting from integer to varchar or varchar to int is severaltimes faster.3) Most significantly, and less obvious, CASE expr WHEN .... recomputes exprfor each WHEN condition, unfortunately, and is the same speed (or perhapsslightly slower) as listing WHEN expr = value for each condition. Perhaps anindexed computed column (somehow materialized) would be advisable whenpossible to avoid repeated computations in CASE..WHEN expressions (if thathelps..).Note that if you divide by COUNT(*), most timings below are below onemicrosecond per row, so this all may not be very significant in mostapplications, unless you do frequent aggregations of some sort.COUNT(*) FROM [my_sf_table] = 477446 rowsThe result from each query = either 47527 or 47527.0Platform: Athlon 2000 XP w/512MB RAM, table seems to be cached in RAM, SQL2000, all queries run at least 3 times and minimum timings shown (msec).SRP is a REAL (4 bytes)Fastest ones are near the end.CPU SQL(ms)-- Convert to varchar (implicitly) and compare right two digits-- (original version -- no I didn't write it)4546 select sum(case right(srp,2)when '99' then 1 when '49' then 1 else 0 end)from sf-- Use LIKE for a single comparison instead of two, much faster-- Note that the big speedup indicates that-- CASE expr WHEN y then a WHEN z then b .-- recalculates expr for each WHEN clause2023 select sum(case when srp like '%[49]9' then 1 else 0 end)from sf-- Floating point method of taking a modulus (lacking fmod/modf)2291 select sum(case round(srp - 100e*floor(srp*.01e),0)when 99 then 1 when 49 then 1 else 0 end)from sf-- Round to nearest 50 and compare with 491322 select sum(case round(srp-50e*floor(srp*.02e),0)when 49 then 1 else 0 end)from sf-- Divide by 49 by multiplying by (slightly larger than) 1e/49e811 select sum(floor((cast(srp as integer)%50)*2.04082E-2))from sf-- Integer approach without using CASE731 select sum(coalesce(nullif(sign(cast(srp asinteger)%50-48),-1),0))from sf-- My original integer approach651 select sum(case cast(srp as integer)%100when 99 then 1 when 49 then 1 else 0 end)from sf-- Modulus 50 integer approach without CASE481 select sum((cast(srp as integer)%50)/49)from sf-- Modulus 50 integer approach460 select sum(case cast(srp as integer)%50when 49 then 1 else 0 end)from sf-- bigint without CASE531 select sum((cast(srp as bigint)%50)/49)from sf-- bigint with CASE521 select sum(case cast(srp as bigint)%50when 49 then 1 else 0 end)from sf-- get SIGN to return -1 or 0, then add 1-- much better than the coalesce+nullif approach500 select sum(sign(cast(srp as integer)%50-49)+1)from sf-- SIGN with BIGINT551 select sum(sign(cast(srp as bigint)%50-49)+1)from sfBTW, I know srp should be int to begin with for this to be faster... Okay,so...select cast(srp as int) srp into sf from [my_real_sf_table]720 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf339 select sum(1+sign(srp%50-49)) from sf310 select sum(srp%50/49) from sf300 select sum(case srp%50 when 49 then 1 else 0 end) from sfWhat if it were a char(7)?select cast(cast(srp as integer) as char(7)) srp into sf2 from[my_sf_table]801 select sum(case right(rtrim(srp),2) when '49' then 1when '99' then 1 else 0 end) from sf2717 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2405 select sum(srp%50/49) from sf2391 select sum(case srp%50 when 49 then 1 else 0 end) from sf2How about varchar(7)?drop table sf2select cast(cast(srp as integer) as varchar(7)) srp into sf2 from[my_sf_table]581 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2569 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2LIKE is faster on VARCHAR than on CHAR columns...Apparently it has to effectively RTRIM the trailing spaces during the LIKEoperation.Is binary collation any faster?drop table sf2select cast(cast(srp as integer) as varchar(7))COLLATE Latin1_General_BIN srpinto sf2 from tbl_superfile561 select sum(case right(srp,2) when '49' then 1when '99' then 1 else 0 end) from sf2530 select sum(case when srp like '%[49]9' then 1 else 0 end) from sf2Binary collation comparisons are slightly faster, though it's not a bigdifference (with just two characters being compared).662 select sum(case convert(binary(2),right(srp,2))when 0x3439 then 1 when 0x3939 then 1 else 0 end) from sf2-----------5037 select right(srp,2) srp,count(*) from my_sf_tablegroup by right(srp,2)order by right(srp,2)920 select cast(srp as int)%100 srp,count(*) from my_sf_tablegroup by cast(srp as int)%100order by cast(srp as int)%100---On the one hand, premature optimization can be a waste of time and energy.On the other hand, understanding performance implications of variousoperations can help write more efficient systems.In any case, an indexed computed column or one updated on a trigger couldvirtually eliminate the need for any of these calculations to be performed,except upon insertion or update, so maybe my comparisons aren't verymeaningful for most applications, considering we're talking about less than3 microseconds per row here worst-case.But the results remind me, some recommend avoiding Identity when it's notnecessary. I find Identity(int,1,1) to be a nice, compact surrogate key thatis useful for quick comparisons, grouping, etc, and so on. Also, it seemsmost appropriate as the primary key to all lookup tables in a star schema inOLAP data warehousing. (?) Of course, in some situations, it's notappropriate, particularly when having a surrogate key violates dataintegrity by allowing duplicates that would not be allowed with a properprimary key constraint, or when the surrogate key is completely redundantwith (especially a short) single-column unique key value that would be abetter selection as the primary key. With multi-column primary keys, I thinkit's sometimes convenient to have a surrogate Identity if only for INclauses that reference that identity column (though EXISTS can usuallyreplace those, so maybe that's a weak excuse for an extra column.)
View 4 Replies View RelatedHello,
I define the sql-query:
SELECT myColumn FROM myPersonsTable WHERE myColumn=@FirstName
then I declare some SqlParameters like:
SqlParameter par=new SqlParameter();
par.ParameterName="@FirstName";
par.Value="John";
mySqlCommand.Parameters.Add(par);
Showing the mySqlCommand.CommandString it will look like:
(A) SELECT myColumn FROM myPersonsTable WHERE myColumn=@FirstName
Is this the real string that is send to the Database (among with some hidden string "John")?...
...or does the real string which is send to the Database look like:
(B) SELECT myColumn FROM myPersonsTable WHERE myColumn='John'
???
If so, where can I retrieve that last string (B)?
Or is (A) the way 'transact sql' operates?
Henk
Greetings,
I have a SQL Server 2000 database using Integrated Security. Users are added directly to the database from their domain name. In the Login section of the Security node in Enterprise Manager they show up as DomainNameUserName. I have four users that have the System Administrator database role. When I use the user function to get their user name they show up as dbo. Is there a way to get their actual domain user name?
Thanks.
A problem so simple I'm a little embarrassed to post it... I am setting up a trigger in which I want to, among other things, record the network ID of the user who performs the action that triggers the trigger. I know there is a variable or function out there to pull that information, but I can't find it, and have tried every variation of "login logname usr_id network_id....." I can find.
So first.... could somebody please tell me the variable or function that will return that piece of information. And second, if you also could point me to a good place, either in print or on line, to look up that kind of information, I would be most grateful.
Thanks.
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies View RelatedHey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies View Relatede.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
I was hoping there would be a newbie section in here... But I'll post anyway (And sorry in advance if its a really dumb question)I am using VWDE and have a SQL DB which has been imported from one of my existing forums, when it shows in the database explorer its show like belowtblArcade (jdName)tblArcadePB (jdName)etc..etc...What I want to know is why this DB has (jdName) after the Table name and my other DB's shown don't?? They are just shown as tblArcade
tblArcadePB etc..etc.. My main questions are "How do I remove the brackets from the forum DB" as when I try to preview data in VWDE I get a message Invalid object name 'tblArcade'.I know its to do with this extra bit as it only happens to the tables that have this on the end... Hope that makes sense and someone can help me, if you could reply as if I'm really dumb it would be appreciated
I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.
View 3 Replies View RelatedHi,I want to access the real execution plan via my webapplication after I have executed an SQL statement. I know how to get the estimated execution plan:1 cmd.CommandText = "SET SHOWPLAN_XML ON";2 cmd.ExecuteNonQuery();3 4 cmd.CommandText = myStatement;5 SqlDataReader dataReader = cmd.ExecuteReader();6 7 String plan = String.Empty;8 9 while (dataReader.Read()) {10 plan += dataReader.GetSqlString(0).ToString();11 }12 13 cmd.CommandText = "SET SHOWPLAN_XML OFF";14 cmd.ExecuteNonQuery();I want do compare the estimated costs with the real costs of the same statement. If I change code line 1 an 13 to "SET STATISTICS XML [ON|OFF]" the string "plan" will contain the result of the submitted SELECT statement, but I just need to get the plan and not the result itself. Thanks in Advance,Dominik
View 6 Replies View RelatedHi All,
What is the difference betwen real and float datatype of SQL server.
Please one exapmle for each.
The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters.
Thanks
Abdul
I have a datagrid that is used to insert values into a table. Before
the insert occurs I want to be able to check that there are no
identical values already in the table. eg. I am inserting names, so am
looking for last name duplicates only.
If a duplicate is found, it should return an error message to the page.
My question is, can I do this duplicate checking via SQL script ie. IF
EXISTS etc... and have the error message returned from there, or do I
need to grab a dataset of the table and look for duplicates before even
going as far as the database with the new value to be inerted?
If the best approach is to catch the duplicate error after executing
the SQL script, how do I grab the error and return it to the page with
the datagrid? I'm sure this is pretty basic, but I am only just now
getting into it.
SQL Server 2000 is being used here.
Thanks!
We are using SQL 7 at 2 different locations, on 2 different Domains that are connected via a T1 line.
We need to have our 2 SQL servers be identical at all times, meaning when a change is made on one server, it is immediately replicated to the other. The problem is that both servers are always being used, so there is no primary/backup server scenario.
Does anyone know if there is a third party software available, or if this is even possible to do, given the fact that the databases are always open ?
Thanks.
Hello,
Can anyone guide me. which is the best method for real time synchronisation of my production server. Is it Transactional Replication or Stand By Server?
Thanks In Advance
Sejal
Hi,
tell me please how I can trace the modification on the table such as "insert" record into one and syncronize mirror table at the same time once the insert has happend, BUT - no indexes no trace jobs, no any modification or objects on the master table... ha?
When I cast Real to Money it makes some changes to the number
DECLARE @i REAL = 989500000.00;
SELECT CAST(@i AS MONEY)
the result is : 989500032.00
what should I do for stop this ?
I have two database, one is for production and another for reporting database, in this Scenario we need to transfer the real time data to reporting database, which replication we need to use and what at the bottelnecks in this..can any one give some suggesstions or give some links...
Thanks in advance
(Spam Removed.)
View 4 Replies View Relatedi have 2 DB in 2 different servers.
db1 and db2
db1 gets 150k records every hour. db2 is like the backup of db1. db2 is used to retrieve records to create reports using reporting services.
what is the best way to synchronize db2 with db1?
is it to create a dts package and set up a job that runs every hour?
off topic question.
what kind of language does reporting services use to create the reports?
hi guyz ...i m new to sql server so is there anyone who could guide me abt sql server 2005 ....i need some info abt ssis ,ssas ...i have got a proj n have sme ques regd tat so plz let me know ur no or mail id so that i could contact u ..plz its urgent
raghu
hi guyz ...i m new to sql server so is there anyone who could guide me abt sql server 2005 ....i need some info abt ssis ,ssas ...i have got a proj n have sme ques regd tat so plz let me know ur no or mail id so that i could contact u ..plz its urgent
raghu
Hi
Does SQL Server support RAC and if yes from which version??
Thanks