SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help on datediff


help on datediff

Author
Message
Nita Reddy
Nita Reddy
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 395
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
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 979
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
Nita Reddy
Nita Reddy
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 395
how do I pass day 16 in the code
Nita Reddy
Nita Reddy
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 395
I tried using cast I am still getting same error
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 979
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 :-D



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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40008 Visits: 38564
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.

Cool
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)
a4apple
a4apple
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 406
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.. :-P

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 2306
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.. :-P


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


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


ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8008 Visits: 7162
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
bharatnarang19
bharatnarang19
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search