Changing Database Server Locale Date Time Settings ?
Jun 15, 2007
Hi There
We currently have the following scenario:
4 app servers with regional date and time settings of locale A.
1 database server with locale settings B.
What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.
It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?
In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?
Thanx
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
Hello,I just installed a SQL server and playing with it. I was reading up onCollation and tried rebuilding the master database after changing toLatin 1. After the master database was built, I looked at the regionalsettings and realized that it was still English. When I tried tochange the settings again using rebuild database, I saw that myselection was gone and replaced by the default setting. Iam a newbieand any help is appreciatedThanks!Sunder
View 1 Replies
View Related
Mar 19, 2007
Hello all,
I have created a simple package that imports data from a flat file into a database. To run the package I'm using a SQL Server Agent Job. The location of the file is stored as a connection string in the Connection Managers tab in the SQL Server Agent Job.
Is there a way to change this connection string programmatically? If not programmatically, is there a way to change this setting right before I execute the package. I want to change the location of this file based on user input. Also, I'm executing the package using the sp_start_job stored procedure to run the job.
Thanks in advance for any advice!
-Dwayne
View 1 Replies
View Related
Oct 19, 2007
hi
I have in my table a field called MyDate with data in this format
MyDate ID
18/02/2007 04:22:32 p.m. 10189844
18/02/2007 04:22:34 p.m. 10189846
18/02/2007 04:22:34 p.m. 10189847
18/02/2007 04:23:34 p.m. 10189849
I need to update some many records in the field MyDate.
What function parameters allow me to change the Date part (from 18/02/2007 04:22:32 p.m. to 25/06/2007 04:22:32 p.m.) without affecting the time part?
And what function allows me to change the Time part (from 18/02/2007 04:22:32 p.m. to 18/02/2007 09:10:00 p.m.)
without affecting the Date part?
regards,
View 8 Replies
View Related
Mar 29, 2005
hi guys
im having real problems and dont know how to solve it at all
i have a web app which allows users to enter information through edit boxes
when they submit the imformation it gets added into my SQL database.
does anyone know how to get the Date and Time when they insert the information and store in another column of type datetime in SQL database
the web app is written in C#
hope someone can help
thanks
View 4 Replies
View Related
Jun 30, 2007
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
View 3 Replies
View Related
Mar 30, 2007
I need an SQL string that inserts the current date into a database.
So far I have tried:
SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')"
mycomm = New SqlCommand(sql, myconn)
mycomm.ExecuteNonQuery()
However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
View 1 Replies
View Related
Jul 27, 2007
With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?
For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.
My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?
Thanks in advance for your help!
PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.
View 6 Replies
View Related
Jun 5, 2006
Hello all. I created an app locally with VWD Express and moved it to to our server but I am getting the following error...
An attempt to attach an auto-named database for file D:WebApp_DataSurvey.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
This is the connectionstring from the web.config file used on the server. I did not change anything from the local copy.
<connectionStrings>
<add name="SurveyConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Survey.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
What settings do I need to change in order for this to work? Any advice is appreciated. Thanks.
View 1 Replies
View Related
Jan 23, 2004
Hi,
I was wondering how to change the language settings on SQL Server. The server is currently set to US English, and I was wondering how it is possible to change it to UK English. The Server Regional Settings are set to UK English.
Also, on a semi-related topic, does SQL Server store dates in a universal format, and then when data is requested does it 'translate' these dates into the format required by that particular regional setting?
Thank you for any input
Marman
View 1 Replies
View Related
Apr 5, 2006
Hi all,
I need to change the Language Settings from my query analyser. Is it Possible???
If so, how ??
Thanks in advance
Regards,
satish.r
"Known is a drop, Unknown is an Ocean"
View 1 Replies
View Related
Apr 4, 2007
How do i change data type settings in when building my tables?
Specifically i want to reduce the number of decimal places on 'smallmoney' so it doesn't show 4 but rather 2. Thank you.
View 2 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Apr 23, 2015
I have following table structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FAS_LEDGER](
[TID] [INT] IDENTITY(1,1) NOT NULL,
[TDATE] [DATETIME] NOT NULL,
[code].....
in this table I have 1571182 rows
the problem is someone did some changes in this LEDGER table
I can insert new rows in this table but when I try to fetch latest rows on the basis of following query then I did not get the latest row.
means
following query gives all the rows of this table
SELECT * FROM dbo.FAS_LEDGER ORDER BY TID DESC
and when I try to filter Master_code = '02-07-01-008-0001' and apply oder by TDATE I do not get latest rows
SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TDATE DESC
View 4 Replies
View Related
Sep 8, 2010
Script to Reverse Engineer / Script out your EXISTING database mail settings?
I set up a profile to use gMail, and it seems logical for me to export out the settings to a script, then run the script on my laptop, other servers, etc.
There's no built in option, so I figured i'd ping the forum before i do it myself.
There are example scripts where you fill in the blanks, examples how to set up dbmail, but i did not find anything that scripts out existing settings.
View 9 Replies
View Related
Jul 19, 2002
SQL 2k w2k server sp2
When I first installed the SQL server on my server, the default date
format was mm/dd/yy. Now I need to change that to dd/mm/yy in
regional settings, but its still not reflected in the DB's in SQL (its
still mm/dd/yy).
Is it possible to change it in SQL without a complete reinstallation
of the server?
View 1 Replies
View Related
Jun 3, 2007
Hi, I m using ASP.NET with C#. I m having one field which should store the datetime of the system. The datetime should be automatically stored for that entry when the user submits that record on the click event.Then it should display the date time on the gridview from database.I m using sqlserver 2005 and i have created the stored procedure for the insert command.This
is the sample sp what should be written here to insert system date time
automatically when the user submits the asp.net form ?Is there any code for writing directly in stored procedure or asp.net coding page... ALTER PROCEDURE [dbo].[StoredProcedure1]@salesid INT OUTPUT,@salesdate datetime,@customername varchar(20)ASBEGINSET NOCOUNT ONBEGIN INSERT INTO sales (customername) VALUES (@customername) SELECT @companyid = SCOPE_IDENTITY()END SET NOCOUNT OFFEND Thanxs in advance...
View 2 Replies
View Related
Apr 10, 2007
Pls tell me,can we store or determine the date/time of a database backed up or can we store date/time while taking back up of a database
View 2 Replies
View Related
Oct 13, 2005
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 Replies
View Related
May 16, 2008
I have a SSIS data flow task that downloads data from an Oracle source which has some real dates and some date values of 0001-01-01 which I am trying to convert to '1900-01-01' using a decode(Date1,'0001-01-01','1900-01-01',Date1). The problem I am having is that when I run the package in Business Intelligence Development Studio on my local machine the date value is stored correctly in the SQL table in a datetime field as 1/1/1900 12:00:00 AM, however when I run the package from the server the field gets saved as 1/1/2001 12:00:00 AM.
I have tried a bunch of ways to work around this issue and the only solution I have found that works it to download into a temp table and then load into the live table. Does anyone have any idea of what might be causing this issue when I run the package from the server?
View 1 Replies
View Related
Jan 5, 2001
I acciently change the locale id of my sql server using sp_configure. After i stop the server it now can not be re-started. Any help is appreciated.
View 1 Replies
View Related
Jun 3, 2014
I am new to sql database programming. developing an application using C# and sql server 2005. i am having problems with date insertion to database. I use datatimepicker control to get date input. here is my code. in table i use datetime column type.
strSQL = "Insert Into TB1 (PPT_No,Reference_No,Application_Date,Receipt_No,Citizenship,Purpose_Visit,Entry_Type,Visa_Category,Airline,Vessel_No,Date_Arrival,
Date_Departs,Collected_Date,Remarks) Values('" +txtPPT_No.Text+ "'," +application_Date.Value+ ",'" +txtRecieptNo.Text+ "','" +cmbcitizenship.Text+ "','"
+txtpurpose.Text+ "','" +cmbentry.Text+ "','" +cmbcategory.Text+ "','" +cmbAirLine.Text+ "','" +txtvesel.Text+
"'," +arrivalDate.Value+ ",'" +departsDate.Value+ ",'" +txtrefference_No.Text+ "'," +collected_Date.Value+ ",'" +txtremarks.Text+ "'";
all date fields i used datetimepicker control. where i went wrong?.
Error : "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
View 4 Replies
View Related
Feb 11, 2006
Hi
I would like to get more information about using detail view together with auto date/time.
I am designing user input form as follow.
tid : uid : tool : priority: reticle: status: remarks: request time : <- to autogenetrate current timeInsert Cancel
How to use date/time function inside detailed view, insert template.
Thanks.
View 2 Replies
View Related
Apr 3, 2008
I have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
View 16 Replies
View Related
May 4, 2015
Are there anyway I can check my last database reorg date and time using Tsql ?
View 5 Replies
View Related
Apr 2, 2008
OK Here is what I am trying to do... In one of my processes I need to take a field IE CREATE_DATE and change that from an EPOCH (number of seconds since Jan 1, 1970) to a human readable date... IE a 04/02/2008 08:23:36 AM and stuff... Now I do know how to use the dateadd... However all my times are coming up as GMT I need them to be EST/EDT... How can I script this to automagically subject 5 or 4 from the hour depending on if we are in daylight savings. This has been plagueing me for the longest time.
Thank you all for any help you can give.
View 3 Replies
View Related
Aug 18, 2007
I have SQL Server 2005 Developer Edition. And my operating system is Windows Vista.
I have written a procedure in C# and deployed it on SQL Server. However when i try to run it i get the following error.
System.Data.SqlClient.SqlException: The locale identifier (LCID) 16393 is not supported by SQL Server
Also I tried using same procedure in Windows XP machine and it workd fine. Can anyone help me what could be the problem?
View 12 Replies
View Related
Jan 24, 2008
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
Thanks,
Steve
View 3 Replies
View Related
Dec 3, 2007
hi all,
in my sql server 2005 , i 've a table gg containing a column 'date'.its data type is datetime ,null
now i want to insert only the date to this field from vb.net page. i dont want "time" to insert in this page. one more thing ,i know varchar will work for what i need but i want to use date time. If anyone who knows how this is possible pls do send me the code.
thanks swapna
View 10 Replies
View Related
Jun 2, 1999
Hi folks,
I posted the following question on comp.databases.ms-sqlserver,
bu, to my surprise got no responses. So I wondered if you good
people might be able to help us out...
==================================================
Our application communicates with SQLServer 7 through ODBC. In
order to write date/time fields into the database, it uses either the dt
conversion function, or more usually the ts conversion, on a string of
the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
other database engine (I think Informix is the only exception), this is
accepted and converted into a date format that the back end can store.
With SQLServer 7, any inserts containing dates are dropped because the
timestamp conversion isn't working. If I take a copy of the select statement
into the query analyser, it reports an error unless I change the timestamp
string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
hh:mm:ss.xxx', ie from microsecond to millisecond resolution.
Version 6.5 didn't have this problem, and as I mentioned, nor does any other
database engine that our application talks to.
================================================== ===
Anyone any ideas???
Cheers,
Geoff.
View 3 Replies
View Related
Oct 22, 2004
I have been searching the net to see how to do this. I'm a noobie to DB's and I may have missed it. OK I have a Customer table and what I want to do is have a column with the date and time that the customer was added to the database ( i.e. Customer Since ) also last time that record was edited. Possible??
Thanks in advanced
Brad Nichols
View 3 Replies
View Related
Mar 12, 2008
Is there a better way of doing this?
DECLARE @PriorDayDate as varchar(10)
DECLARE @CurrentDayDate as varchar(10)
DECLARE @StartHourTime as varchar(10)
DECLARE @EndHourTime as varchar(10)
DECLARE @PriorDayDateANDStartHourTime as varchar(20)
DECLARE @PriorDayDateANDEndHourTime as varchar(20)
DECLARE @CurrentDayDateANDStartHourTime as varchar(20)
DECLARE @CurrentDayDateANDEndHourTime as varchar(20)
SELECT @PriorDayDate = convert(varchar,(GETDATE()-1),126)
SELECT @CurrentDayDate = convert(varchar,(GETDATE()),126)
SELECT @StartHourTime = convert(varchar,(dateadd(hh, -1, getdate())),108)
SELECT @EndHourTime = convert(varchar,getdate(),108)
SELECT @StartHourTime = left(@StartHourTime, 2) + ':00:0000'
SELECT @EndHourTime = left(@EndHourTime, 2) + ':00:0000'
SELECT @PriorDayDateANDStartHourTime = @PriorDayDate + ' ' + @StartHourTime
SELECT @PriorDayDateANDEndHourTime = @PriorDayDate + ' ' + @EndHourTime
SELECT @CurrentDayDateANDStartHourTime = @CurrentDayDate + ' ' + @StartHourTime
SELECT @CurrentDayDateANDEndHourTime = @CurrentDayDate + ' ' + @EndHourTime
--select @PriorDayDate
--select @CurrentDayDate
--select @StartHourTime
--select @EndHourTime
--select @PriorDayDateANDStartHourTime
--select @PriorDayDateANDEndHourTime
select cast(@PriorDayDateANDStartHourTime as datetime) as 'PD Start Time'
select cast(@PriorDayDateANDEndHourTime as datetime) as 'PD End Time'
select cast(@CurrentDayDateANDStartHourTime as datetime) as 'CD Start Time'
select cast(@CurrentDayDateANDEndHourTime as datetime) as 'CD End Time'
View 14 Replies
View Related
Oct 22, 2015
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?
View 2 Replies
View Related
