SQL Error, Checking For A Date
Nov 24, 2007
Hello,
i have a very simple table called room....
Room_ID Date
1 20/11/2007 00:00:00
and i have a very simple SQL SELECT statement...
SELECT * FROM room WHERE date = '20/11/2007 00:00:00'
i have tried several variations of the query but i either get an error or a null dataset.
can anyone explain where im going wrong ?? i have read about casting but still i cannot return a dataset where i have a date in the statement.
Any help is much appreiciated !!
Truegilly
View 1 Replies
ADVERTISEMENT
Oct 5, 2000
I am facing a funny problem. I am doing a select to get records with "ExitDate" greater than "FromDate".
However, when I run the query, it is also returning the "FromDate".
SET @FromDate = '3/31/2000'
SELECT IndividualID, ExitDate FROM clientserviceprogram
WHERE exitdate IS not null
AND EXITDATE > @FromDate
I am still getting records with ExitDate = '3/31/2000'.
What am I missing out on?
Thanks in advance!
View 2 Replies
View Related
Nov 2, 2005
Although I am familiar with SQL commands I have never worked with SQL server before and find myself at a loss for which direction to take. I have a database for equipment reservations. I need to write a procedure to notify the end-user if a specific piece of equipment is already checked out during the time frame requested. I'm reading up on stored procedures and triggers and not sure which one (if either) is appropriate to use. Any suggestions? Thanks,
View 1 Replies
View Related
Nov 6, 2013
Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap as I can't have two prices on one day.
For example if in a table there was:
Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00
The top two overlap.I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:
SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]
[code]....
View 5 Replies
View Related
Dec 17, 2007
Hi friends,
I am new to SQL developement ( am .Net developer). I need to implement a stored proc in sql 2005 on CC expiration task.
I have CC exiration date.
By using expiration date (like '01/08'),
Conditions are:
1. If CC expiring within 31 days. Stored proc will write the Customer data in EmailNotification table with message type =1. (Customer will not get second notification until 14 days before expiring)
2. If CC expiring within 14 days. Stored proc will write the customer data in EmailNotification table with message type =2. (Customer will not get second notification until 11 days before expiring)
3. If CC expiring within 11 days. Stored proc will write the customer data in EmailNotification table with message type =3. (Customer will not get second notification until 2 days before expiring)
4. If CC expiring within 2 days. Stored proc will write the customer data in EmailNotification table with message type and update orderStatusID to On Hold CC Expired in order table.
My Tables are
1.
TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerCode] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateOrdered] [datetime] NOT NULL,
[OrderTypeID] [int] NULL,
[OrderStatusID] [int] NULL,
[LastProcessDate] [datetime] NULL,
2. CREATE TABLE [dbo].[Payment](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[OrderPaymentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardNumber] [varbinary](max) NULL,
[Last4DigitsOfCreditCardNumber] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpirationDate] [varbinary](max) NULL,
[CVV2] [varbinary](max) NULL,
[Amount] [numeric](18, 2) NOT NULL
3. CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] AS ([dbo].[udf_FormatCode]('RS',[CustomerID])),
[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NOT NULL,
[MembershipTypeID] [int] NULL
Please atleast basic structure of stored proc code. It will greatful to me.
Thanks in advance
srikanth
View 3 Replies
View Related
Jan 6, 2015
I have a table called as Events and below are its columns
ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar
IsHoliday bit
This is a master table where the admin would enter the Events/Holidays for the entire year.
The data would be as below:
IDEventFromdateEventTodateEventDesc isHoliday
126-01-201526-01-2015RepublicdayYes
230-01-201531-01-2015TeamOutingNo
301-05-201501-05-2015Labour day Yes
Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"
How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015
Can we exclude a non-working day or a sunday.
Leavedetails table to insert leaves applied by employee is as follows
LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar
View 0 Replies
View Related
Mar 16, 2015
I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
View 7 Replies
View Related
Feb 6, 2005
Hi Guys,
SQL Server 2000
I've run out of ideas on what to do with a DTS process that has suddenly started giving an error when one company is processed on it.
Here's the deal:
DTS package launches a complex set of stored procedures (built entirely without error handling) as a step in importing data from various customers. I have isolated the error to one segment where I have to cursor through records that have been identified as having changes. Unfortunately, this cursor is updating about 15 system tables and uses modular stored procedures, about 12 of them, to do the job.
Error: The classic Subquery returned more than one value.
Oddity: If I run the code in Query Analyzer I never get an error, the code finishes without complaints. If I run the exact same code via the DTS package I always get the error. Note that the cursor appears to process every row assigned to it and then the sproc throws the error.
What I've Tried:
1) The obvious, reviewing the code for any unprotected subqueries. Haven't found it.
2) Checking for @@Error = 1 at key points in the code to try to isolate where this is happening. Can't catch it.
Note that this same process is used for several other company's data import process and they do not error even when running the DTS package. Therefore I'm assuming it is a data issue with the one company that errors that is causing the problem.
Any suggestions on what to try next would be greatly appreciated!
Thanks!
View 2 Replies
View Related
Feb 20, 2003
Hi,
Just a brief question. I have a script which does a number of insert statements. What I would like to do is determine if the insert statements were all successful. Aside from checking @@ERROR after every insert, is there a way to check if all the insert statements completed successfully?
Thanks,
Jim
View 2 Replies
View Related
Nov 9, 2007
I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return
END
ELSE
PRINT 'Success'
This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return = @failure
END
ELSE
PRINT 'Success'
Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following:
Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied. Number:201 Severity:16 State:4
So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.
View 7 Replies
View Related
Jun 2, 2004
Hi all,
I have a stored procedure that inserts records into the database. It has error checking that checks for transaction failure, and if it happens it rollback the transaction.
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
My question is how can I test if the error checking works?
View 6 Replies
View Related
Mar 12, 2007
Hi All,
I have a stored procedure to which I am adding an error checking. Here is my stored procedure.
CREATE PROCEDURE usp_DBGrowth
AS
DECLARE @dbsize DEC(15,2)
DECLARE @logsize DEC(15,2)
DECLARE @dbname SYSNAME
DECLARE @dbsizestr NVARCHAR(500)
DECLARE @logsizestr NVARCHAR(500)
DECLARE @totaldbsize DEC(15,2)
DECLARE @dbid SMALLINT
DECLARE dbnames_cursor CURSOR
FOR
SELECT name, dbid
FROM dbo.sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'
EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
PRINT @dbsize
SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 2'
EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
PRINT @logsize
SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
PRINT @totaldbsize
BEGIN TRANSACTION
IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
@dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)
INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
Can you tell me what I am doing wrong?
Also, I need to add error checking after execute sp_executesql statement. Any suggestions?
View 2 Replies
View Related
Mar 12, 2007
Hi All,
I have a stored procedure to which I am adding an error checking. Here is my stored procedure.
CREATE PROCEDURE usp_DBGrowth
AS
DECLARE @dbsize DEC(15,2)
DECLARE @logsize DEC(15,2)
DECLARE @dbname SYSNAME
DECLARE @dbsizestr NVARCHAR(500)
DECLARE @logsizestr NVARCHAR(500)
DECLARE @totaldbsize DEC(15,2)
DECLARE @dbid SMALLINT
DECLARE dbnames_cursor CURSOR
FOR
SELECT name, dbid
FROM dbo.sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'
EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
PRINT @dbsize
SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 2'
EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
PRINT @logsize
SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
PRINT @totaldbsize
BEGIN TRANSACTION
IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
@dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)
INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
Can you tell me what I am doing wrong?
Also, I need to add error checking after execute sp_executesql statement. Any suggestions?
View 14 Replies
View Related
Feb 23, 1999
Hi all,
We have been checking the database by dbcc checkdb,through a automated task
on our production server (nightly task run everday)d we always getting the message given below.
Msg 625, Level 20, state 1
Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0.
If anyone knows about this error message, can you kindly post the message.
Thanks a lot.
Jay.
View 1 Replies
View Related
Feb 3, 2005
This maybe belongs in the Data-Access Forum, but I'm not sure.
Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.
Are there performance/coding issues involved? Best practices?
View 1 Replies
View Related
Feb 26, 2006
Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
View 1 Replies
View Related
Jan 3, 2008
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure:
Code: ( text )
View 4 Replies
View Related
Jun 8, 2007
I have a table with amount columns and I want the amount column to either insert the value from the database or a zero based on a condition.
For the table rows I use the following to find the amount:
=iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0)
which works fine. However, when I try to Sum in the group foot I get #Error when I use
=sum(iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) )
for the groupings that have a type other than Material and Other. For some reason, it doesn't total the amounts of Material and Other with the Zeroes that were placed in the table rows based on the Condition.
For example, the subtotal errors out when trying to total Material with Labor but if it was just Material and Other, it works.
Example of what the Columns are:
job, year, month, type, amount
s57, 2007, 2, labor, 0
s57, 2007, 2, material, 500
month total errors out
year total errors out
job total errors out
Any help would be appreciated.
View 12 Replies
View Related
Mar 22, 2007
Originally got this error as the reason some of my .rptproj files could not be converted from SRS 2000 to 2005. Now I€™m getting this error when checking out a report from Visual Source Safe 6.0€¦any clue as to what it means?
Project item '4294967294' does not represent a file.
This is for a solution created and stored in VSS using VS2003 and SRS 2000. Now trying to open with VS 2005.
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies
View Related
Feb 19, 2006
hi, my Date field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks
no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()
Author = myReader("Author")
myReader.Close()
myConn.Close()
lblShow.Text = Subject
View 2 Replies
View Related
Mar 16, 2004
Hi:
I got the next problem, when I try to modify a record of my SQL Server Database from my Delphi application the next message error appears
"Date is less than 01/12/2003"
The record that I'm trying to modify was inserted from the same applicaition.
I'm not so sure if it's a database problem, but I don't know why it is passing. What can I do?
Thaks for your help!!
Cristopher Serrato
View 1 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Jun 1, 2000
In Ms-Access table I have the field called
date which yyyymmdd
the data I have in Ms-Access is
19940101
year month day.
Ms-Access data type is date and ms-Sql datatype is datatime. but still i'm getting error any help
What should be the Ms-Sql data type filed ?
When I try to conver from Ms-ACCESS to MS-SQL Server it give me this error
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime
View 2 Replies
View Related
Jan 26, 2007
Hi Playing around on server at new giggerooo. SELECT CAST('2007-01-28'ASDATETIME)throws:Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.This:SELECT CAST('20070128'ASDATETIME)Works fine. ASFAIK (or recall) former is SQL Standard, latter is ISO. Should I just start using the ever-so-slightly-less-readable YYYYMMDD format or is there a specific setting I should be looking at and perhaps change? SS 2KLanguage: English (United States)Collation: Latin1_General_CI_AI Ta:)
View 10 Replies
View Related
Oct 7, 2014
while using following function I am getting this Error "Msg 195, Level 15, State 10, Line 1 'DATE' is not a recognized built-in function name."
select callid, DATE(segstart) AS calldate
from October_CLI.dbo.raw
Where callid >0
Segstart Column = Data with call date & time
Callid Column = Unique Call Id
October_CLI.dbo.raw = Table
Calldate = Where i want the only date to be capture from segstart column
View 4 Replies
View Related
Mar 4, 2006
hi guys,
i am passing date value from a textbox in c# and asp.net.
i am calling an sql procedure .
The result is binding to a datagrid.
my sql procedure is like this
create procedure searchComplaintdetails
(
@FirstName nvarchar(50),
@DueDate DateTime
)
as
DECLARE @SQL varchar(5000)
SET @SQL = 'select Customers.CustomerFirstName as Name,ComplaintLog.LogDate,ComplaintLog.LogID,
ComplaintLog.ComplaintStatus,ComplaintLog.DueDate,ComplaintCategories.CategoryName from ComplaintLog
Join ComplaintCategories on ComplaintLog.CategoryID=ComplaintCategories.CategoryID
join Customers on ComplaintLog.CustomerID=Customers.CustomerID
where ComplaintLog.IsActive=1'
IF Datalength(@FirstName)>0
SET @SQL = @SQL + ' AND Customers.CustomerFirstName LIKE ''' + @FirstName + '%'''
IF Datalength(@DueDate)>0
SET @SQL = @SQL + ' AND Convert(DateTime(10),ComplaintLog.DueDate,101) = '+@DueDate + ' '
EXEC (@SQL)
GO
my error is Syntax error converting datetime from character string.
if i pass nothing in test boxit will show another error
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
View 4 Replies
View Related
Sep 27, 2007
I am trying to import a table from Pervasive into SQL 2000 however I am getting an error due to a date column with some dates of 0000-00-00. How can I import the data without errors?
View 1 Replies
View Related
Jan 31, 2008
Good Morning Forum
First time poster here, but I have gleaned much needed support using the forum in the past, so
many thanks to all.
Well, on to business.
I have a strange problem with a T-SQL stored procedure my company uses for reporting.
Around the Christmas holiday period, it just stopped working.
I managed to track the error down to the code below:
(Just the code to recreate the error.)
declare
@g datetime,
@g2 datetime
select @g2=getdate()
select @g=convert(datetime,convert(varchar(10),@g2,101))
select @g,@g2 , SERVERPROPERTY('PRODUCTVERSION'), SERVERPROPERTY ('PRODUCTLEVEL'), SERVERPROPERTY ('EDITION')
When this is run, I get this error message:
Msg 242, Level 16, State 3, Line 6
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the About information from .
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.1.2600
I know this is not the best way to use datetime or conversions, but I did not write it, I am just supporting it.
The strange thing is, the above code has worked every week since January 2006?
The even stranger thing is, it runs on perfectly well on Server 2000 and on the version below that has not had
any recent service packs installed.
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
Is this a bug, or have I lost the plot?
Thanks in advance for any feedback.
P.S: The style code 101, if changed to 103 allows the code to work. But, when apllied to the pre-RTM server, is
returns the same error message.
View 7 Replies
View Related
Feb 10, 2004
Hi,
I want to assign a list columns to be the PK on a table. I don't know what this list of columns would be.How do I go about figuring that?
Sample Data
Col1...Col2...Col3..Col4
1...........2.......A......X
1...........2........B.....X
1...........2.......A......Y
As you can see Col1 & Col2 ONLY do not make up the PK. I need to include Col3 as well. My concern is I am not sure if I have a row like..
1...........2.......A......X reapeated lower down the order. So how do I figure the PK out?
Thanks
View 2 Replies
View Related
Apr 26, 2004
Hi i m tring to convert a date time
declare @a datetime
declare @b varchar(10)
set @b='26/04/2004'
set @a= Convert(datetime, @b)
but it gives me this error:
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I believe is my date format..i want to know how to make sure that i am the dd/mm/yyyy format is correct way?
View 3 Replies
View Related
May 2, 2002
Hi,
I'm using ODBC (System DSN) to connect to SQL Server 7.0 Database.
Whenever I try to connect to SQL Server Database from my client
application, I'm getting the following error message -
----------------------------------------------------------
Microsoft SQL Server has reported the following error:
[Microsoft] [ODBC SQL Server Driver] Invalid Date format
----------------------------------------------------------
After this message, I'm getting another error message -
----------------------------------------------------------
Cannot create a record in table SysConfig (SysConfig).
The SQL Database has issued an error.
----------------------------------------------------------
Subsequently, I get another error message -
-----------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Invalid date format [INSERT INTO SYSCONFIG
(CONFIGTYPE,ID,VALUE,MODIFIEDDATE,MODIFIEDTIME,MOD IFIEDBY,CREATEDDATE,CREATEDTIME,CREATEDBY,RECID
) VALUES (?,?,?,?,?,?,?,?,?,?)]
-----------------------------------------------------
The application that I'm trying to connect from is a package from 3rd
Party. I do not have any control over it.
But I think the problem could be from the side of SQL Server. Can someone
look into this please?
Thanks,
Harish
View 1 Replies
View Related
May 27, 2004
Hi,
I importing data from db2 into sql
using openquery
In some case I have problem with date
example
DB2 date = '2004-05-13 24:00:00.000000'
I tried to convert it manually
sql server
select cast('2004-05-13 24:00:00.000'as datetime)
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
do I have to substruct - 1 millisecond ?
View 1 Replies
View Related
