I recently wrote a stored procedure to add the date and time to an existing table. The date is being stored as
Oct 29 2007 5:37PM
Instead of as
10/29/2007 5:37:23 PM
The column is a varchar(24) data type and I use GETDATE() in the stored procedure to supply the date.
What do I need to do to change to get this to save the date as mm/dd/yyyy
Miranda
Hi,I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.What is a good way to not save blank date fields as "01/01/1900"?ThanksStephen
View 1 Replies View RelatedHi guys, I was doing some changes on my asp web pages, I added new field expiry date that will be scanned into the system in format ddmmyyyy but it can't save as i am gettting that message that char can't be converted into datetime as date time was out of range.
I have added this into my asp pages <% Session.LCID = 3081 %> and changed the sql server regional setting setting the date to dd/mm/yyyy I can't set it to ddmmyyyy as there is not option without "/".
Could somebody help me as this is quite frustrating.
Thanks
For some reason a stored procedure which I have created is incorrectlysaving the date to the table. It seems the day and month are beingswapped around e.g. a date which should be the 12th April (12/04/2005)is saving as the 4th December (04/12/2005).The parameter used in the stored procedure comes from a VB6 app, Iamended this so the format was "yyyymmdd hh:mm:ss". The full line in VBbeing,Parameters.Append .CreateParameter("date_of_call", adChar, , 17,Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))When I run my VB app it works fine, the syntax in the stored procedureis,CREATE PROCEDURE dbo.spUpdValues@data_id int,@date_of_call datetimeasupdate dataSET date_of_call = CONVERT(char, @date_of_call, 101)where data_id=@data_idIs it because the convert format is using an american date format ? Ican't see why as I can't reproduce this error using my own PC as thedate saves correctly, I can also confirm it's not happening to everybodywho uses the app. If it is happening for specifc users then what couldbe the cause. I've checked Regional Settings and all seems fine there.Any ideas on what could be doing this as I'm struggling to investigateany further.To debug I ran the stored procedure direct, manually inputting thevariable - again no problem. Also, the following SQL statment shows noproblem...declare @date_of_call datetimeset @date_of_call = '20041101 08:30:00'select CONVERT(char, @date_of_call, 101)select CONVERT(char, @date_of_call, 106)------------------------------11/01/2004(1 row(s) affected)------------------------------01 Nov 2004(1 row(s) affected)Any help would be much appreciated.*** Sent via Developersdex http://www.developersdex.com ***
View 10 Replies View RelatedThis is a pretty simple question, but I'm going nuts trying to find the answer. After creating an SSI package, I told it to save to the SQL server... Now where do I go to pull that package up again and make changes and/or execute the package?
View 4 Replies View RelatedI don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.
That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.
Thanks.
How can I get the number of months (or years or days...)
between the current_timestamp
an a date in the DB2 format ?
DateDB2 Char(26) = 2000-12-30-12.55.30.123456
DateSQL = GetDate() or Current_Timestamp
Hello All,
I've been struggling with this for a few weeks now. I have this table with the following information:
08/10/2006 10:31:13 AM
08/14/2006 1:32:10 PM
38895.396528
38916.584873
38917.5639
38922.398727
38919.482083
9/20/2006 12:01:19 PM
09/08/2006 1:32:02 PM
38894.73316
38918.543229
38926.564954
08/10/2006 12:31:25 PM
08/10/2006 12:31:25 PM
08/09/2006 1:03:25 PM
What I'm trying to do is get these dates w/timestamps (38926.564954) into this format
08/09/2006 1:03:25 PM . Please help!!!!! Thanks.
Hi,
I'm totally new at SQL Server, I'm sorry if this question looks very simple, but I just don't know how to change the datetime value in a field on my database.
I'm working in vb.net and I have a datetime field in sql server, I can store date values passing this value:
DatePick.Value.ToString("s")
dates are saved fine, but if want to make this query:
Select * from backlog where date1>=isdate('21/02/2006') order by date1 asc
I get all the values from that field, no filter is done. Date are shown like this 2004-05-23 14:25:10.000 in the query.
What do I have to do ? do I have to change the date format of my field to ISO 8601 ? how ? do I use a script or what ???????
Please remeber that my sql script should be done from vb.
I really appreciate your help.
George
if
(datalength
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD])
) = 1)
then
(0 ||
cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
else
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
Goal:
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
hi Experts,what is the purpose writing a date in the following format:where x.event_date >= {ts '1980-01-01 00:00:00'}versus like this:where x.event_date >= '1980-01-01 00:00:00'what benifit does it add to later form of writing?Thanks in advance.schal.
View 4 Replies View Relatedok so this is my table structure
[odcCode] [int] NOT NULL ,
[insCode] [varchar] (11) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[odcQty] [int] NOT NULL ,
[odcCostoUnitario] [numeric](18, 4) NOT NULL ,
[odcISV] [bit] NULL ,
[odcStatusInsumo] [varchar] (3) COLLATE Modern_Spanish_CI_AS NULL ,
[odcInsArrivalDate] [datetime]
INSERT INTO OrdenCompraDetalle
(odcCode, insCode, odcQty, odcCostoUnitario, odcISV, odcStatusInsumo, odcInsArrivalDate)
VALUES
(697,'MIN-000028',1,22.3250,0,'PEN', '18/10/2007 12:00:00 a.m.')
and when I run, the insert command I get the next error:
Syntax error converting datetime from character string.
The date is taken from a standar DateTime Picker control from Visual Studio 2003 and I can't change the regional settings on the computers (that's not an option)
What can I do to fix the problem and be able to insert the date in any format?
I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.
For example:
Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04
When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!
I would like to be able to get it to be 59:40:03.
Any help would be much appreciated.
Hi,
I'm currently having problems exporting formatted dates from reporting services 2005 to excel.
Basically what I require is a way to format a date in reporting services so that it only shows the date without the time (preferably british format) and when it is exported to excel it is still formatted as a date.
This is so the user can sort the data file via date, I appreciate it is easy to select the column and format the cells but i would prefer to have a 'cleaner' solution to this problem which avoids the need for users to be formatting exported reports.
Originally I was formatting the dates as convert(varchar,@date,103) in the SP which converts it to a character string and excel picks this up as a character as would be expected. So I changed this to a date and set about trying to format the date in Reporting Services, so far i've been unsuccesfull using cdate (brings back the time) and format as it again converts it to a character string.
Any help or advice would be greatly appreciated,
R
Hi,
I am having the "classical problem" of the forums; A local date changed to the american format (normally in the development environment) which I call "Switching months with days"... well it switches both again if you press "view report" again :)The problem I'm having is that when I Navigate from one report to other, the dates get switched... Everything's been set in the locale uk date format (dd/mm/aaaa), in the operating system, in the database and in the reports... and the reports are working properly through the web interface.. the only thing that is not working properly is the Navigation which switches the date month with the day...Changing all the Reporting Services server, database server to the american format date is not an acceptable solution. Thanks for any guidance on this, as I'm pretty lost...Jose
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
View 0 Replies View RelatedI have created range bar chart and I am not able to achieve the following tasks.
1. Change X-axis Label Format to Quarter:
I have x-axis with dates and y axis of project groups. I have changed x-axis interval type = month and interval=3.
 Â
Set the Maximum = Â Max(ProjectEndDate) and Minimum = Min(ProjectStartDate).
Now my chart showing 3 months x-axis interval dates in mm/dd/yyyy format. I want to change this interval date format to Quarter. The problem is LabelsFormat property is not recognize  the "=Q or q or quarter" and also not accepting the expressions. How can I achieve this?
2. Placing series side by side when it is not overlapping
I want to place the same group series side by side only when the previous project end date is less than next project start date, otherwise place the next project to next row. How can I achieve this?
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
Can someone tell me where DTS Packages are saved by default? Is it the MSSQL7/Binn directory?
Thank you,
Michelle Turner
When I submit data through my website one particular field which is drop down field and has 4 values (5, 10, Fee, other) saves as "undefined"
I get no errors through the web site.
Field is varchar
I recently viewed an application which included customizable savable reports. In the first step, the user selects from a very large (probably over a hundred) set of variables to include in the report (these would be the columns displayed in the report). In a second step, the user selects (from the same set of variables) which variables they want to set as criteria indicators (these would then be dropdowns and text boxes on the report that would filter the query results).
So, how do they do this? Lets take the AdventureWorks database as an example. Lets say the user can select the following variables to include in their report:
Product.Name
Product.ProductNumber
Product.Color
Product.Size
Product.ListPrice
WorkOrder.StartDate
WorkOrder.EndDate
WorkOrder.OrderQty
PurchaseOrderDetail.DueDate
PurchaseOrderDetail.UnitPrice
PurchaseOrderDetail.OrderQty
Vendor.Name
Etc…
How do you design this in the database? If you have a table that holds all of the possible variables, and a table that connects the user-selected variables to the user – how then do you construct the query that pulls it all together? For example, what if a user wants only these two variables in their report:
Product.Name
Vendor.Name
Here is the query:
select p.name as ProductName, v.name as VendorName
from Production.Product as p
inner join Purchasing.ProductVendor as pv on pv.ProductID = p.ProductId
inner join Purchasing.Vendor as v on v.VendorId = pv.VendorId
How do you build that query from just getting two variable names?
ALTER TRIGGER Trigger1
ON countries
FOR UPDATE AS
begin
insert into country1 values(:deleted.cid,:deleted.cname,getdate(),,system_user,@@servername, host_id(),host_name(),DB_NAME())
--select * from country1
/*insert into country1
select * from deleted*/
i want to store data into country1 table as old data with all information
end
IF I WANT TO SAVE THE ABOVE TRIGGER ERROR OCCURS
IAM USING SQL SERVER 2005
HELP ME
Malathi Rao
I created a DTS by right clicking and exporting the data to a file and I also saved the DTS package. Where will the DTS Package be saved in SQL 2005 in case if I have to do any modifications later.
Thanks !
I don€™t understand what
my wrong is. I am trying to save data in to SQL Mobile Database. my Database is included in my project. after saved, i connect that database by VS2005 server explorer to view the data. But I don€™t found any data in Sdf file.
private void cmdSave_Click(object sender, EventArgs e)
{
SqlCeConnection objmycon = new SqlCeConnection(); objmycon.ConnectionString = ("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\SC.sdf;"));
SqlCeCommand objcmd = new SqlCeCommand();
objcmd.Connection = objmycon;
objcmd.CommandText = "Insert Into BenInfo (BenID, BenName, BenDOB, BenRegDate, BenExpDate) " +
" Values(@BenID, @BenName,@BenDOB,@BenRegDate,@BenExpDate)";
objcmd.Parameters.Add(new SqlCeParameter("@BenID", SqlDbType.NVarChar, 6));
objcmd.Parameters.Add(new SqlCeParameter("@BenName", SqlDbType.NVarChar, 50)); objcmd.Parameters.Add(new SqlCeParameter("@BenDOB", SqlDbType.DateTime, 8));
objcmd.Parameters.Add(new SqlCeParameter("@BenRegDate", SqlDbType.DateTime, 8));
objcmd.Parameters.Add(new SqlCeParameter("@BenExpDate", SqlDbType.DateTime, 8));
objcmd.Parameters["@BenID"].Value = this.txtID.Text;
objcmd.Parameters["@BenName"].Value = this.txtName.Text;
objcmd.Parameters["@BenDOB"].Value = this.dtpDOB.Text;
objcmd.Parameters["@BenRegDate"].Value = this.dtpRegDate.Text; objcmd.Parameters["@BenExpDate"].Value = this.dtpExpDate.Text;
objmycon.Open();
objcmd.ExecuteNonQuery();
MessageBox.Show("Beneficiary Information Save Successfully.");
objmycon.Close();
}
please advice me.. how can i over come this situation....
Hi!
I want to modify a package stored on the server from my client machine, and everything goes great.
The only problem I get is that when I try to run it by double-clicking it I have to re-write the password in the connection string, i.e. "pwd=asdasd", because it is not saved by the package itself even though the checkbox in the Connection Manager tab (in the SSIS Designer) is checked. What can I do to save it automatically??
Thanks
Hello All,
I am facing this unusual error. In My ASP Page I am submitting the Page and after submitting the form I am calling another ASP page, which contains sql command to insert the record.
My Problem is when I am submitting the Form. Record is getting inserted twice with same values.
What is the cause of this problem as I am facing this problem in every form.
With Regards
Sunil Dutt
Hi, there;
I have a SSIS package which has SQL2005 as its destination. When I setup the connectionmanager, I'd like to use SQL authentication. But I found that the password field cannot be saved (Checkbox "Save password" is selected).Everytime I open the this package's connectionmanager, that password field is cleard. The result is that my C# application that loads that package to execute failed with "Login failed..."
Did I do something wrong or anybody know how to save that password field?
Cheers
I have a developer using SQL 7.0 Enterprise Manager and the DTS Wizard to create and save simple DTS imports on the SQL Server. The DTS job runs only on his workstation using his NT logon ID. Server is set for mixed security.
Other users can edit the saved DTS job and resave it, then anyone can run the job. I tried having the developer log in with his NT logon ID on another workstation; he can't run the job from there if he was the last person to edit it from his workstation.
Any ideas, anyone? This is the only person having this problem in my shop.
Thanks!
I was trying to save a long text from asp.net application to "text" data type in sql database but it gets cut off at few hundred characters.
Does anyone know what the problem is?
Thanks for any help.
I have saved a DTS Package, in sql serv 2005. How do i run it ? I cant fiqure it out
View 5 Replies View Related