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?
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.
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.
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.
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
Is there a way to strip off the time portion of a datetime datatype without changing the datatype? I know I can convert it using CONVERT (NVARCHAR(10), dbo.tblPayments.PaymentDate, 101) but I need to keep it as a datetime datatype?
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).
In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.
The package and report run fine. One of the columns in the table is for a date (date of birth). The SSIS package gathers the column information and inserts it into SQL Server as a Decimal datatype (Decimal 6,0). The dates (decimals?) are now in the format 40207, as they were on the 400, where that specific date would be April 2, 2007. 120707 would be December 7, 2007 and so on. I would like to format the date to be mm/dd/yy or even m/dd/yy for the "single digit" months in the report for easy readability.
I have tried setting the format options in reporting services for formatting the field as a date, and even using the cdate conversion or FormatDateTime in an expression. I cannot seem to get the date to change. I either get an "# error" for the values of the fields or I get the same date that already was there (40207)?
It seems as if none of the date formatting options are working. Is this something I need to do on the package (set the mappings on the create table to create the columns as a Date, not Numeric or something similar?), or should it be easier than that by converting the output of the report to display mm/dd/yy with the "/"?
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:
AuditID - the audit action (insert, modify - old, modify - new, deleted) AuditDate - date and time of action AuditUser - User who did it...
At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.
I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...
Question: Whats better for performance:
1. Separating out the date and time and doing a clusterd index on the date.
2. Keeping date and time in the same column and just use a normal index.
Aim – Currently i have column “[LAST-STATUS-CHG]” in the following date format “042312” i need this date format to be changed into “23-04-12”.
Also i would like an additional column created called “Start of month” this column should look at “[LAST-STATUS-CHG]” and what ever the day is in the defauly it to 01
Current results FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHG 87800000088416042312 87800000088513011212 87800000088612100712
Where can i change the date stored internally to SQL Server. It is this value which is displayed when we execute getdate() function. But, Changing the system date doesn't change the result of getdate() function. I want to get a different value for getdate(). Is it possible ?
I live in Australia and use the date format of - dd/mm/yyy, but when i do a query in SQL Management it seems to want its inputs in US format mm/dd/yyy.
For example it errors when i do a search and one of the dates is 27/11/2007, ie there is no 27th "US" Month.
I have checked my local settings (regional and language in control panel), and it all points to Australian formats, it just seems the SMS wants the dates in US.
Hello. I am using Microsoft SQL Server Management Studio (SQL Server 2005). When I select a date column from a table, the date is displayed in "mm/dd/yyyy hh:mm:ss" format. Is there a way i can change this date format so that it shows "dd/mm/yyyy hh:mm:ss" permanently? Thanks.
I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt.
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value from my_table where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.
how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000
I have a SELECT statement, the result of which populates a datagrid. The first column has consecutive dates in it and I want to hyperlink each date to a seperate Javascript function (the Javascript is created on the fly and is unique for each date). I need a different function name for each function and so tried the date but "/" is not allowed in the Javasript function name. I think the easiest way will be to produce a new column with the date expresses ddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-
"CASE " & _ "WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]
but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.
I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!
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?
