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
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: 1095 Visits: 411
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2402 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
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: 1095 Visits: 411
how do I pass day 16 in the code
Nita Reddy
Nita Reddy
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: 1095 Visits: 411
I tried using cast I am still getting same error
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2402 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 Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98610 Visits: 38996
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
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1451 Visits: 2308
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
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20434 Visits: 7428
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) 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.
bharatnarang19
bharatnarang19
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

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