.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Datetime saved in Sql server is changed

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :ASP.Net
 

Hello,

There are several text fields on our web forms. The fields can be either empty or a date. If it is empty we will assign a default date value and save it in sql server 2005. The default value is

 DateTime dt = new DateTime(1900, 1, 1); 

Before yesterday, the data is saved as "1900-01-01 00:00:00" in the database server, but it was suddenly changed and saved as  "1900-01-01 12:00:00" that is PM and 12 hrs different from the old values 00:00:00. Our DBA monitored the insert process and found the value passed from web to database is "1/1/1900 12:00:00", so sql server saved it as "1900-01-01 12:00:00". The DBA said the value passed is "1/1/1900 00:00:00" before the issue. We have to changed the code to  

DateTime dt = new DateTime(1900, 1, 1, 0, 0, 0); 

That fixes the issue and date saved back to "1900-01-01 00:00:00". The thing I don't understand is that we didn't have code / Server changed that night, why everything is changed suddenly and strangely. The insert script generated from the web server with either old code or modified code is "insert into tablesname values('1/1/1900 12:00:00 AM')"

 

Does anyone have this similar issue before or can someone tell me what cause the datetime is changed during the executenoquery?

 

Thanks in advance!




View Complete Post


More Related Resource Links

Essential SQL Server Date, Time and DateTime Functions

  
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

datetime server

  

Hi,

I'd like to have the date and time from server.

DateTime.Now Is ok?

Must be something else?


thanks.




Server drafts location not being saved Excel 2007

  
Hi, I have problem with "Server drafts location" not being remembered. And my "Server drafts location on this computer" button is not even checked but I still get the message: "The server drafts location you entered for offline editing is not valid or you do not have permission to access that location. Please specify a location on your local computer" Even if I change and choose a place for this it renders this message as soon as I for example "Adjusted List". Every thing I change in "Excel Options" renders the above message. I have full control over the "Application data" folder as well. Please see if you can help out since this is really giving me trouble in my daily work. I have Microsoft Windows XP professional sp 3 and Office 2007 latest sp. Thanks in advance! Mari

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

  
I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

SQL node remove failed on Cluster :The state of your SQL Server installation was not changed after t

  
HI guys , Have you faced this .I am not able to remove the SQL Server 2008 ENT node .The error is : Overall summary:   Final result:                  Failed: see details below   Exit code (Decimal):           -2068643838   Exit facility code:            1203   Exit error code:               2   Exit message:                  The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.   Start time:                    2010-09-12 09:40:40   End time:                      2010-09-12 09:48:26   Requested action:              RemoveNode The details.txt shows the same error in a different way : Running Action: CloseUI 2010-09-12 09:44:44 Slp: Stop action skipped in UI

Any negative impact to Sharepoint and Project Server if IP for backend database server is changed.

  

If IP address of the backend database server will be changed for sharepoint 2007 and project server 2007, will it impact any way  both the servers.


Converting SQL Server Datetime to MySQL Datetime

  

I am trying to store a SQL Server datetime into MySQL datetime field, but MySQL stores the date value as all zeros.  I  use this function to trim the fractional part from the SQL Server datetime, but get the same result when trying to store it in MySQL.

Dim tDate As String = sqlDate.ToString()
        tDate = tDate.Substring(0, tDate.Length - 4)
        Return tDate


 


SQL Server DateTime Convert pattern

  

I need the datetime to be in the following format for SQL server 2005

dd/MM/YYYY HH:MI in 24 Hour Format

note: seconds are not included

 

Thanks for any help in advance


Conversion failed when converting datetime from character string. SQL Server @StartDate as datetime

  

Hi,

Can you please help me on this?

When I try to pass my parameter it doesn't work. See below is my code :

 

declare @SQL Varchar(4000)

declare @CRM_FilteredAppointment nvarchar(2000)

declare @CRM_FilteredSystemUser nvarchar(2000)

declare @StartDate datetime

declare @EndDate datetime





set @CRM_FilteredSystemUser = 'Select * from FilteredSystemUser'

set @StartDate = '20080101'

set @EndDate = '20100101'



set @CRM_FilteredAppointment = 'SELECT activityid, participationtypemask 

FROM FilteredAppointment 

WHERE FilteredAppointment.eu_calltype = 2

AND FilteredAppointment.statecode = 1

AND FilteredAppointment.scheduledend >= DateAdd( month, -1, GetD

what should be changed to web.config file if i deploy my application to actual server ?

  

Hi,

I have developed a web site application with vb.net code behind.Debugging mode was on.

I have deployed my web site in actual server machine ,but when i open my home page some error is coming related to web.config file.I directly deployed my application to actual server without any change to web.config file.

So, is there any settings required if i deploy my application ? 


Convert NVARCHAR column to DateTime - SQL Server

  

Hi there,

Have spent few hours to get this working. I like to get help now. Please note MONTH AND DATES stored in single or double digits as they occur.

Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010,  22/10/2010 etc. Following UK Date here.

It is required to pull the data to COUNT No. of Expired. Obviously below one fails.

--AS Date AND Month stored in Single digits AND double digits , below statement 
--thorws Arthmetic overflow exception
SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND    
 CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired  


 


Convert NVARCHAR column data into DATETIME - SQL Server 2000

  

ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.

NOW IT IS REQUIERD TO RETRIEVE ALL RECORDS WHERE EXPIRYDATE BETWEEN USER PASSED START MONTH,START YEAR AND END MONTH, END YEAR.

USING SQL SERVER 2000

SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
		CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired

 

Your help is greatly appreciated. Spent hours to get this resolved.


A

Convert NVarchar to DateTime - SQL Server 2000

  

Hi there,

FROM USER INTERFACE END USER SELECTS START MONTH, START YEAR AND END MONTH, END YEAR. IT IS REQUIRED TO RETRIEVE DATA WHERE ONE OF THE FIELDS I.E, ExpiryDate is of type NVARCHAR, stored dates as 2/10/2010 [DD/MM/YYYY] , 10/10/2010 ETC.

Using SQL Server 2000.

ALTER PROCEDURE [dbo].[CountUsers]
		(
			@startMonth nvarchar(20) =	NULL,
			@startYear nvarchar(20) = NULL,
			@endMonth  nvarchar(20) = NULL,
			@endYear  nvarchar(20) = NULL,
			@startDate nvarchar(50) = NULL,
			@endDate  nvarchar(50) = NULL
		)
		AS BEGIN

		SET @startDate = @startYear + '-01-' + @startMonth; 
		SET @endDate  = @endYear + '-01-' + 

How can I download an Excel file saved on the same server as an asp.Net website?

  

Hello,

I able to successfully see all the Excel files in the given folder using the datagrid and code below. Now I would like to be able to click the link to the file, or add a button, to get a download file dialog box. Can someone please help me.

Thanks!


MarkUp:


<asp:datagrid id="FileGrid" runat="server" 
    AutoGenerateColumns="False" AllowSorting="True">
    <Columns>
        <asp:TemplateColumn HeaderText="File" >
            <ItemTemplate>
                <asp:HyperLink id="systemLink" runat="server" 
                NavigateUrl='<%# DataBinder.Eval(Container, "DataItem") %>'
                Text='<%# DataBinder.Eval(Container, "DataItem") %>' />
            </ItemTemplate>
        </asp:TemplateColumn>
        <asp:BoundColumn DataField="Length" HeaderText="File Size" />
        <asp:BoundColumn DataField="LastWriteTime" HeaderText="Last Modified" />
    </Columns>
</asp:datagrid> 



CodeBehind:


Imports System.IO

Partial Class pageclass

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend