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

Number of days between two integer date columns Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 12:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:44 PM
Points: 16, Visits: 55
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.
Post #1426397
Posted Monday, March 4, 2013 12:14 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
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))



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 #1426401
Posted Monday, March 4, 2013 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1426408
Posted Monday, March 4, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1426409
Posted Monday, March 4, 2013 2:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:44 PM
Points: 16, Visits: 55
It worked, Thankyou.
Post #1426475
Posted Monday, March 4, 2013 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:44 PM
Points: 16, Visits: 55
Lynn yours worked, that is what i needed, Thanks again
Post #1426478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse