Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help on datediff Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 8:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 437, Visits: 352
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
Post #1560448
Posted Thursday, April 10, 2014 8:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 588, Visits: 895
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) < 212

Also notice that I created a table and some sample data to test with please do the same on future questions. Thanks




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560452
Posted Thursday, April 10, 2014 8:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 437, Visits: 352
how do I pass day 16 in the code
Post #1560456
Posted Thursday, April 10, 2014 8:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 437, Visits: 352
I tried using cast I am still getting same error
Post #1560462
Posted Thursday, April 10, 2014 8:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 588, Visits: 895
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




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560470
Posted Thursday, April 10, 2014 8:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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 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


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1560478
Posted Thursday, April 10, 2014 9:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1560488
Posted Friday, April 11, 2014 3:51 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 1, 2014 2:42 PM
Points: 990, Visits: 2,218
a4apple (4/10/2014)
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..


That is not what OP is doing. It's more like:

SELECT CONVERT(DATE, '20140101', 16); --Error
SELECT CONVERT(DATE, CURRENT_TIMESTAMP, 16); --Works

Post #1561137
Posted Friday, April 11, 2014 4:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,030, Visits: 3,027
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1561145
Posted Saturday, April 12, 2014 5:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 4:01 AM
Points: 1, Visits: 4
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
Post #1561244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse