March 4, 2013 at 12:07 pm
Hi Friends,
I have a requirement where i have to write a Sql for :
basically i have 2 columns 'startdate_SK' and 'enddate_SK' columns in table 'A'
'startdate_SK' is a integer ex:99971231
'enddate_SK' is a integer ex: 17530101
requirement: need to find the number of days between the above two columns
ex: 'startdate_SK' - 20130101 and 'enddate_SK' - 20130201 then my result should be 31
IMP:: both columns are integers in the table.
i can use datediff but they are not date columns instead they are integers.
Please suggest, any help is appreciated,
--
Thanks,
Sam.
March 4, 2013 at 12:14 pm
sunder.mekala (3/4/2013)
Hi Friends,I have a requirement where i have to write a Sql for :
basically i have 2 columns 'startdate_SK' and 'enddate_SK' columns in table 'A'
'startdate_SK' is a integer ex:99971231
'enddate_SK' is a integer ex: 17530101
requirement: need to find the number of days between the above two columns
ex: 'startdate_SK' - 20130101 and 'enddate_SK' - 20130201 then my result should be 31
IMP:: both columns are integers in the table.
i can use datediff but they are not date columns instead they are integers.
Please suggest, any help is appreciated,
--
Thanks,
Sam.
This: datediff(day,cast(cast(startdate_SK as varchar(8)) as date), cast(cast(enddate_SK as varchar(8)) as date))
March 4, 2013 at 12:26 pm
sunder.mekala (3/4/2013)
Hi Friends,I have a requirement where i have to write a Sql for :
basically i have 2 columns 'startdate_SK' and 'enddate_SK' columns in table 'A'
'startdate_SK' is a integer ex:99971231
'enddate_SK' is a integer ex: 17530101
requirement: need to find the number of days between the above two columns
ex: 'startdate_SK' - 20130101 and 'enddate_SK' - 20130201 then my result should be 31
IMP:: both columns are integers in the table.
i can use datediff but they are not date columns instead they are integers.
Please suggest, any help is appreciated,
--
Thanks,
Sam.
[rant]
ACK!!! Why do so many people continue to use incorrect datatypes? We have a datetime datatype, that is what should ALWAYS be used for datetime data.
[/rant]
You are going to have to convert your integers to datetime first.
Something like this should work for you.
;with cte (startdate_SK, enddate_SK)
as
(
select 20130101, 20130201
)
, convertedDates as
(
select convert(datetime, (stuff(stuff(cast(startdate_SK as char(8)), 5, 0, '/'), 8, 0, '/'))) as startdate_SK,
convert(datetime, (stuff(stuff(cast(enddate_SK as char(8)), 5, 0, '/'), 8, 0, '/'))) as enddate_SK
from cte
)
select datediff(day, startdate_SK, enddate_SK)
from convertedDates
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 12:28 pm
Looks like Lynn beat me to the punchline. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 2:59 pm
It worked, Thankyou.
March 4, 2013 at 3:01 pm
Lynn yours worked, that is what i needed, Thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply