April 10, 2014 at 8:19 am
I have first column as char and 2nd column as date please see if I am doing right or wrong I am getting conversion string error :Conversion failed when converting date and/or time from character string
column defination
Verdate is char type and
secdate is date is datetype
my syntax
where datediff(DAY,CONVERT(date,verdate,16), CONVERT(date,secdate,16)) < 212
April 10, 2014 at 8:23 am
Try using CAST instead like so:drop table #datediff
create table #DateDiff (
Verdate char(10) null
,secdate date null)
insert #DateDiff
values ('20140101', '20140201')
SELECT *
FROM #DateDiff
where datediff(DAY,cast(verdate as date), secdate) < 212Also notice that I created a table and some sample data to test with please do the same on future questions. Thanks
April 10, 2014 at 8:28 am
how do I pass day 16 in the code
April 10, 2014 at 8:32 am
I tried using cast I am still getting same error
April 10, 2014 at 8:36 am
Nita Reddy (4/10/2014)
how do I pass day 16 in the code
I'm not sure what you are trying to do now. Are you comparing the values between the two columns (which is what my script does) or are you trying to pass in a specific date (which is in your original request). Please read the first article of my signature and please provide the scripts to create a table and insert some sample data and then the desired output. That way we don't have to read each other's mind 😀
April 10, 2014 at 8:41 am
Nita Reddy (4/10/2014)
I have first column as char and 2nd column as date please see if I am doing right or wrong I am getting conversion string error :Conversion failed when converting date and/or time from character stringcolumn defination
Verdate is char type and
secdate is date is datetype
my syntax
where datediff(DAY,CONVERT(date,verdate,16), CONVERT(date,secdate,16)) < 212
Please post the DDL (CREATE TABLE statement) for the table and some sample data (series of INSERT INTO statements (some people still use SQL Server 2005)), and the expected results based on the sample data.
From what you have posted all we can do is guess since we can't see your tables or data from here.
April 10, 2014 at 9:06 am
Nita Reddy (4/10/2014)
how do I pass day 16 in the code
What do you mean to say, when you want to pass 16 in the code. It's not even a valid one? Is it 106?
SELECT CONVERT(VARCHAR(64), GETDATE(), 16)????
Atleast my sql server version doesn't support, may be.. 😛
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 11, 2014 at 3:51 pm
a4apple (4/10/2014)
Nita Reddy (4/10/2014)
how do I pass day 16 in the codeWhat do you mean to say, when you want to pass 16 in the code. It's not even a valid one? Is it 106?
SELECT CONVERT(VARCHAR(64), GETDATE(), 16)????Atleast my sql server version doesn't support, may be.. 😛
That is not what OP is doing. It's more like:
SELECT CONVERT(DATE, '20140101', 16); --Error
SELECT CONVERT(DATE, CURRENT_TIMESTAMP, 16); --Works
--Vadim R.
April 11, 2014 at 4:25 pm
What specifically does the data in the "Verdate" look like?
For example, is it 'yyyymmdd hh:mm'?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2014 at 5:07 pm
The style 16 is not supported for conversions from varchar to date. It is supported for conversion of date to date only
You may use:
declare @verdate as char(10)='01-01-2014'
declare @secdate as date=getdate()
select datediff(DAY,CONVERT(date,@verdate,105), CONVERT(date,@secdate,105))
Bharat Narang
Microsoft Certified Solutions Associate in SQL Server 2012
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply