I'd like to have the date and time from server.
DateTime.Now Is ok?
Must be something else?
View Complete Post
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)
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!
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
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
WHERE FilteredAppointment.eu_calltype = 2
AND FilteredAppointment.statecode = 1
AND FilteredAppointment.scheduledend >= DateAdd( month, -1, GetD
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
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.
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
SET @startDate = @startYear + '-01-' + @startMonth;
SET @endDate = @endYear + '-01-' +
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.