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


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

datetime server

Posted By:      Posted Date: August 21, 2010    Points: 0   Category :ASP.Net
 

Hi,

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

DateTime.Now Is ok?

Must be something else?


thanks.






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.

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..

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


 


Datetime saved in Sql server is changed

  

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!


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

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-' + 

Datetime format checking in SQL server stored procedure

  

Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:

<pre>globalization uiCulture="en" culture="en-GB"></pre>

This helps when we do a date comparison in a static class of ours to return a certain rating factor.

Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data.

Thanks


call JavaScript - jQuery code from ASP.NET Server-Side

  
jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side.

Making Ajax Callbacks to the Server Using jQuery with ASP.NET

  
In the first part of this article series I introduced jQuery's functionality and how it provides a rich client side programming model. This time around I'll expand on these concepts and show you how you can use jQuery in combination with ASP.NET using it as an AJAX backend to retrieve data. I'll also discuss how you can create ASP.NET controls and otherwise interact with jQuery content from ASP.NET pages in WebForms.


jQuery is just a JavaScript library so it will work seamlessly with ASP.NET both from page code as well as through backend driven code using the Page.ClientScript object or ScriptManager. You can use jQuery on its own as a client side and Ajax library that communicates with ASP.NET or you can use jQuery in combination with ASP.NET AJAX. The two actually complement each other quite well as jQuery provides functionality that the ASP.NET AJAX library does not and vice versa. For the most part the interaction between the two libraries is trouble free except for a few very rare edge cases.

MS SQL Server: Disconnect Users From Database - Kill User Session

  
If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
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