How to derived string to datetime (dd/mm/yyyy)
Input Date : Start Date
15/06/2015 30/06/2015
NULL 2015/06/24
Some date are in format dd/mm/yyyy I want to keep them as it is , but I want to convert the NULL one and 2015/06/24 to 24/06/2015. How can I do this .
I tried this :
select distinct CONVERT(VARCHAR(100),[Start Date],103) from it_projects_src
but doesnt work with "2015/06/24"
How to derived string to date-time (dd/mm/yyyy)
Input Date :
Start Date
15/06/2015
30/06/2015
NULL
2015/06/24
Some date are in format dd/mm/yyyy I want to keep them as it is , but I want to convert the NULL one and 2015/06/24 to 24/06/2015.
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
There is a citydate field with string data type in SQL 2K,2004-04-04 20-45-08. I'd like to convert it to a datetime data typewhich should be 12/07/2004 1:34:30PM.Please help.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi
I'm sure this is simple T-SQL but I'm stuck.
I've a bit field named "Active" and a varchar [Name] field
I want to select a derived fields "DisplayName" that consists of the Name plus either "Active" or "Not Active" depending if Active is 1 or 0.
Something like SELECT [Name]+(IF Active THEN ' Active' ELSE ' Not Active') AS DisplayName FROM Lookups ORDER BY DisplayNamebut that doesn't work.
Any suggestions please?
As in the database, i made a few columns in the forum table.
date(datetime) 15/09/2004 3.35PM
author(char) John
Select datetime + '<br>' + author from forum
it claimed there is an error on this datetime.
By right, the result should be
15/09/2004 3.35PM
John
can anyone help me how i could get the result out without having to change date's properties in the sql database?
Will be greatly appreciated if help gets ard.
hi , i have a problem
i have an textarea that i want to convert to DateTime format (dd/MM/yyyy) .
the data in the textarea is (dd/MM/yyyy for example 21/12/2005).
i need it to add this data in sql server , in smalldatetime formation colum .
plz help.
Hi,
Ive imported a data set into SQL. Now on redefining a text field to datetime, most dates that are filled come through okay.
The issue is that there are some empty fields which I'd like for it to stay empty after conversion. Now what happens is that the empty field becomes '01/01/1900' - which is throwing off our queries as we need it to be compared to other date fields.
Is there a way to keep it empty even after the datatype is changed to datetime?
Thanks a bunch!
Hi
Can we convert DateTime to String. If so how?
Thanks in advance.
Mahathi.
Hi
What's wrong about
SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @FY + '/04/01')
AND CONVERT(Datetime, @FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A
I am trying to develop an attendance application which calculates the shift details as per the Clock IN OUT Data , the following are the table details
CREATE TABLE [dbo].[INOUTData](
EmpID VARCHAR(10),
CLockDate Date NULL,
[INTIME] Time NULL,
[OUTTIME] Time NULL,
) ON [PRIMARY]
[Code] ....
-- I am trying to get the data as follows combining these two tables
EMpID ClockDateSession1StartTimeSession1EndTimeActualSession1StartTimeActualSession1EndTime
Session2StartTimeSession2EndTimeActualSession2StartTimeActualSession2EndTime
E1 28-Jan-201408:00:0012:00:0007:50:0012:15:0017:00:0021:00:0017:15:00 20:55:00
E2 28-Jan-201408:00:0012:00:0008:30:00NULL17:00:0021:00:0016:15:0021:55:00
E3 28-Jan-201408:00:0012:00:00NULL 11:34:0017:00:0021:00:0016:15:00 21:55:00
E4 28-Jan-201408:00:0012:00:0008:30:00 11:34:0017:00:0021:00:00NULL 21:55:00
E5 28-Jan-201410:00:0014:00:0010:35:0014:44:0019:00:0023:00:0018:55:00 NULL
Right now i am using a stored procedure which traverses through each record and does the job.
Is there any way to do it with Pivot, or queries instead of cursors.
We are using MS Reporting Services to generate Xmls for corporate billing statements. Our DBAs found that MS ReportingServices are querying Oracle Catalogue before executing stored procedures in RDLs to get Oracle parameter information. Is there a way to eliminate the trip to database before executing the stored procedures?
Hongmei
Hi,
If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.
Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?
Thanks
John
I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).
So I now have the data and I want to derive new columns based on that data using an if statement:
if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"
comptype is the new derived column I am creating.
But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis
I selected the cube element from the columns list in derived element, when it initially put it in it looked like:
[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]
and gave error:
The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Below is the statement I use to get data back from AS
select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')
So can some one help me on how I can do this derived colum.
I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates.
Below is the code that I am using:
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>"
selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq"
insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@RepName, @BranchName, @Date, @DueDate, @ByEmail, @ByFax, @Fax, @ProjExp, @PrintQty, @Disc, @Email, @RepEmail)">
<InsertParameters>
<asp:FormParameter Name="RepName" FormField="LoanRepNameBox"/>
<asp:FormParameter Name="BranchName" FormField="BranchList"/>
<asp:FormParameter Name="Date" FormField="CurrentDateBox" Type="DateTime"/>
<asp:FormParameter Name="DueDate" FormField="ReqDueDateBox" Type="DateTime"/>
<asp:FormParameter Name="ByEmail" FormField="ProofByEmailCheckbox" Type="boolean"/>
<asp:FormParameter Name="ByFax" FormField="ProofByFaxCheckbox" Type="boolean"/>
<asp:FormParameter Name="Fax" FormField="FaxNumberBox"/>
<asp:FormParameter Name="ProjExp" FormField="ProjectExplanationBox"/>
<asp:FormParameter Name="PrintQty" FormField="PrintQuantityBox"/>
<asp:FormParameter Name="Disc" FormField="PDFByDiscCheckbox" Type="boolean"/><asp:FormParameter Name="Email" FormField="PDFByFaxCheckbox" Type="boolean"/>
<asp:FormParameter Name="RepEmail" FormField="LoanRepEmailBox"/>
</InsertParameters>
</asp:SqlDataSource>protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance.
Thank you,
Paul
Hi,I am getting this error: String was not recognized as a valid DateTime.I'm trying to insert data into a table from a gridview, where two of the dates can be empty strings.I have set 'allow null' in the SQL Server table for the two dates, added the culture in the web.config file, and even tried converting the values to DBNull.None of which have worked, and I am still getting the error.This is the code I am using: if (e.CommandName == "EmptyInsert")
{
if (Page.IsValid == true)
{
TextBox txtVisitDateIns = GridView2.Controls[0].Controls[0].FindControl("txtVisitDateIns") as TextBox;
TextBox txtNextVisitDateIns = GridView2.Controls[0].Controls[0].FindControl("txtNextVisitDateIns") as TextBox;
TextBox txtVisitedByIns = GridView2.Controls[0].Controls[0].FindControl("txtVisitedByIns") as TextBox;
DropDownList ddlPriorityIns = GridView2.Controls[0].Controls[0].FindControl("ddlPriorityIns") as DropDownList;
TextBox txtMailshotDateIns = GridView2.Controls[0].Controls[0].FindControl("txtMailshotDateIns") as TextBox;
if (txtNextVisitDateIns.Text == "")
txtNextVisitDateIns.Text = DBNull.Value.ToString();
if (txtMailshotDateIns.Text == "")
txtMailshotDateIns.Text = DBNull.Value.ToString();
SPVisitsBLL visits = new SPVisitsBLL();
visits.AddVisit(Convert.ToInt32(GridView1.SelectedValue.ToString()), Convert.ToDateTime(txtVisitDateIns.Text.ToString()),
Convert.ToDateTime(txtNextVisitDateIns.Text.ToString()), txtVisitedByIns.Text.ToString(),
Convert.ToInt32(ddlPriorityIns.SelectedValue.ToString()), Convert.ToDateTime(txtMailshotDateIns.Text.ToString()));
GridView2.DataBind();
}
} I haven't checked txtVisitDateIns to see if it is an empty string as this date is required.Can anyone help?Thanks
I have a dropdown list thats boudn to a SqlDataSource. The DataSource looks like this:
<asp:SqlDataSource ID="dsProgramList" runat="server" ConnectionString="<%$ ConnectionStrings:csData %>"
SelectCommand="SELECT DISTINCT [Program_Name] +','+ [Begin_Date] AS NAMEandDATE, [Course_ID], [LOC] FROM [ThisTable] WHERE ([LOC] = @LOC)">
<SelectParameters>
Where the dropdownlists text = NAMEandDATE and its value = Course_ID
When I select the LOC from the LOCdropdownlist, the dropdownlist in question updates, and an error "Erro converting datetime from character string" happens?
Any suggestions?
Hi there,
I have the following code:
Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String)
Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String)
Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String)
Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String)
Dim Rent As String = Ctype(Request.Querystring("Rent"), String)
Dim DateToday = DateTime.Now
Dim mySQL AS String
If suburbValue = "- All -" Then
mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "
However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:
Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
Source Error:
Line 30: MyDA = New SqlDataAdapter (mySQL, myConn)
Line 31: myDS = New DataSet()
Line 32: myDA.Fill(MyDS)
Line 33:
Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)
Source File: K:detailsdetailspropertylisting.aspx Line: 32
Any ideas how to resolve this?? I do not want to use Parameters in my statement.
Thanks in advance,
TCM
Can anybody point me to a list of all valid string formats for passing in datetime variables into SQL server. Ideally I just want to put in a date and not bother with a time
View 3 Replies View RelatedI am passing a string to my stored procedure call where I convert it to a DateTime. I have pasted the relevant code below. My trouble is that prior to execution of the stored procedure an error is thrown "String was not recognized as a valid DateTime".
The error does not get thrown when I pass it a date, it only throws it when no date is provided.
The page I am using is a form to do a search. The search can allow a date to be entered or left out. How do I catch an empty value in the c# for an invalid datetime and still send the variable to the stored procedure.
//////////////////////////// C# Code /////////////////////////////////// Passing the Date to a stored Procedure SqlParameter bdpDateFrom = new SqlParameter("@datefrom", SqlDbType.DateTime);bdpDateFrom.Value = Convert.ToDateTime(thisbdpDateFrom);myCommand.Parameters.Add(bdpDateFrom);
///////////////////////// Stored Procedure Code //////////////////////////////////@datefrom datetimeIF ((@datefrom IS NOT NULL) AND (@datefrom <>' '))BEGIN SET @whereclause = @whereclause + ' AND T1.c330101invoicedate_dt >= ''' + convert(varchar,@datefrom) + ''''END Thank you!
Hi all, first time post so go easy.
I have been trying to find a way of creating a datetime comprised of getdate() and a time that i specify. I am trying to do this as shown below:
Declare @Test datetime
Declare @Test1 Datetime
Set @Test = getdate()
SET @Test1 = CONVERT(datetime, datepart(y, @Test) + datepart(m, @Test)+ datepart(d, @Test)+' 16:30:00',120)
So, i am building a string using datepart and then adding the time at the end. The 120 after the final comma is to define the style (as i am sure you will all know anyway).
When i run this i get the following error:
Msg 245, Level 16, State 1, Line 35
Syntax error converting the varchar value ' 16:30:00' to a column of data type int.
If i take out the colons it runs but brings back the wrong date, the whole date seems to be dependant on the time part at the end.
I am sure this is a really simple problem and i am sorry if i am wasting you time but its late and i just cant see it. Any help would be fantastic.
Can anyone tell me what this error message means and how I can correct it on my statement:
Code:
Server: Msg 241, Level 16, State 1, Line 1Syntax error converting datetime from character string.
Here's my query statement:
Code:
SELECT animalid AS "Animal ID", name AS "Name", categoryid AS "Category ID", DATENAME(MONTH, dateBorn) + ' ' + DATENAME(DAY, dateBorn) + ', ' + DATENAME(YEAR, dateBorn) AS "Date of Birth"FROM animalWHERE categoryid = 'Cat' AND dateBorn = 'May%'
I know it has something to do with: dateBorn = 'May%' because when I took out Quote: AND dateBorn = 'May%' I get results, but I need to narrow it down to list the cats born in May.
I have a column which has 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using
update tablename
set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' +
SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));
and then
alter table tablename
alter columnname datetime;
but later it shows up the error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
How do I change it any other opinion or any modification for the above query.
I am trying to convert a date-string of this format (DDMMMYY - ex. 06JAN15) to datetime but I keep on getting errors:
codes that I tried:
convert(datetime, '06JAN15', 6)
convert(datetime, '06JAN15', 112)
What date format code should I use for DDMMMYY?
Stuart writes "Hi being new to this game
I have have an error when trying to inseet string into a table with datetime field.
the date is not that important its the time that I use in later steps
I am creating a global temp table and then inserting values into it
below is the code
-- create the temp table
Execute ( 'create table ##progsch0
([Time] [DateTime] , '
+ '[' + @day7 + '] [varchar](100) ,'
+ '[' + @day1 + '] [varchar](100) ,'
+ '[' + @day2 + '] [varchar](100) ,'
+ '[' + @day3 + '] [varchar](100) ,'
+ '[' + @day4 + '] [varchar](100) ,'
+ '[' + @day5 + '] [varchar](100) ,'
+ '[' + @day6 + '] [varchar](100) )')
set @Starttime = 'JUL 21,2006 5:30am'
I am doing the insert in this manor becuase the @Starttime
in code actually changes time and a new record in inserted into the temp table.
Set @SQL = 'Insert into ##progsch0 (Time)
Values(convert(varchar,Convert(datetime,'+ @Starttime +'),100))'
PRINT @SQL
execute sp_executesql @SQL
I may to doing this in the completely wrong manor.
Any help would be greatful "
I am trying to select all members from a SQL db who have a renewal date btw to dates inputted into two text fields, but I am now getting the error above. This is where the problem is coming in...
If strStartDate <> "" and strEndDate <> "" Then
If bParam = True Then
StrSQL = strSQL & " AND "
Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "
End If
Any help would be greatly appreciated!
Thank you...
Hello all,
New to the forums here. I'm not a beginner with SQL, but nor am I a SQL developer - network engineer who knows some scripting and the fundamentals of DB design/administration. There's the background.
I have a database of client information where a date is keyed in and stored as text. Because this is entered by end-users, the way it's entered varies - 1/1/2001, 01/01/2001, etc. Most use "1/1/2001" (note the date is not the same for each record)
I have 4,000 records to update and I need to try to convert the text string to the correct date - changing 1/1/2005 to the same date in proper date/time format. My database uses datetime as an integer calculating the number of days from 12/30/1800. Today's date would be 75508. Time is separated into different fields.
I can do the work to update the text to a single format to make the conversion easier, but I am having trouble locating the proper way to write a convert function to do this. I've searched online (which is how I came here) and have searched the forums without luck.
Any help would be greatly appreciated!!!
hi,
i have a simple code here but i always get same error.
my code is :
declare @date datetime
declare @dates varchar(30)
declare @day varchar(2)
declare @month varchar(2)
declare @year varchar(4)
set @date = (select dateadd(day,0,getdate()))
set @day = (select datepart(day,@date))
set @month = (select datepart(month,@date))
set @year = (select datepart(year,@date))
set @tarih = (select '''' + @year + '-' + @month + '-' + @day + ' 00:00:00.000''' )
if @month < 10
begin
set @month = '0' + @month
end
if @day < 10
begin
set @day = '0' + @day
end
-------------IT GIVES ERROR HERE
set @dateS = (select '''' + @year + '-' + @month + '-' + @day + ' 00:00:00.000''' )
---
well ,
when i try this >
set @date (which is datetime format ) = '2008-03-22 00:00:00.000'
it works(manually),
but i cant dynamically bind my string to datetime formatted field.
then i tried,
set @dates = (select '''' + @year + @month + @day + '''' )
set @date = (select convert(datetime,@dates)
and it DOESNT work too,
what to do ?
MS BLESS US
Hello, i try to work a my Pocket PC project. I have a question about SQL.
How to write a sql select dataset from table with a give string date but the column is datetime? I don't know how to convert the date string to datetime.Also the time part in the datetime coulmn have some value in it, how to get over that?
and how to make sure both format is correct? because as i check the msdn, there are many different format in datetime.
For example
using date string XX-XX-XXXX to select dataset in the following table
table with following column
DateTimeStamp
XX-XX-XXXX yy:yy:yy:yyyy
Thank you for helping~
When I go to Report > Report Parameters... in VS, I create date parameters and specify the type as datetime. I then add the parameters to my SQL statement. I then go back to look at the parameters and they have changed to string. This happens every time I make a modification to the SQL statement. Is this a bug, or am I missing a step? I thought someone would have asked this before, but I can't find a post for it in this forum.
Thanks in advance,
Scott
I've been trying to do the following:
Code Snippet
if year(@SomeDateTime) <> @SomeYear
set @SomeDateTime= convert(datetime, @SomeYear+ '1231', 112)
If variable @SomeDateTime evaluates to 20080101 and @SomeYear = 2006, I wish that my variable @SomeDateTime becomes 20061231 (December 31st).
The way it's written now, it doesn't work... @SomeDateTIme evaluates to 1908-11-12.... ?!
hi to all:
i got a few questions regarding the SQL server 2005. our company just set-up a replication server so that the reports where creating will be separated from the main server which sap clients are connected.
i have encountered several problems, i have a sample table here
DOCNUM, CARCODE, COMMENTS
--------------------------------------------------------------------
123456 TEST 02.19.07 TEST#123456
ETC.. ETC.. 01.31.07 WR55555555
the problem is, when i convert the remarks to a date, i get what i want but, i cannot filter it once converted in a subquery?
my sample query goes like this
select * from
(select docnum, cardcode,
CONVERT(DATETIME,(CAST(substring(ltrim(table.Comments),1,8) AS DATETIME)), 101)
from table where isdate(substring(ltrim(table.Comments),1,8)) = 1 and table.Comments is not null
)
as a
where a Between '01.01.08' and '04.24.08'
and this also dont work
select docnum, cardcode,
CONVERT(DATETIME,(CAST(substring(ltrim(table.Comments),1,8) AS DATETIME)), 101)
from table where isdate(substring(ltrim(table.Comments),1,8)) = 1 and table.Comments is not null
WHERE
CONVERT(DATETIME,(CAST(substring(ltrim(table.Comments),1,8) AS DATETIME)), 101)
Between '01.01.08' and '04.24.08'
i always get the ff error msg:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
i dont have a problem when were using sql 2000 before, but when we upgraded to sql 2005 its a headache..
please i need your advice. btw the comments column is of nvarchar type and the first 8 characters are ALWAYS of the date format (mm.dd.yy) and does it have a factor on the collation settings? the main server uses the SQL_Latin1_General_CP850_CI_AS while the replicated server uses SQL_Latin1_General_CP1_CI_AS
thanks in advance guys, hope you could help me out..