Help (Date)

Question

Post reply

Help (Date)

najeeb selk

SSC Enthusiast

Points: 103
More actions
March 22, 2006 at 12:27 pm

#112344

I have a table that have date column with some dates such as
DateCol
StringCol
02/03/2006
Test1
03/03/2006
Test2
06/03/2006
Test3
07/03/2006
Test4
08/03/2006
Test5
12/03/2006
Test6
I want to select the date between 02/03/2006 and 12/03/2006 and the result must be as
DateCol
StringCol
02/03/2006
Test1
03/03/2006
Test2
04/03/2006
NULL
05/03/2006
NULL
06/03/2006
Test3
07/03/2006
Test4
08/03/2006
Test5
09/03/2006
NULL
10/03/2006
NULL
11/03/2006
NULL
12/03/2006
Test6
How can I do that please?
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Red8Rain Hall of Fame Points: 3312 More actions · Answer 1

select * from table1 where DateCol between '2/03/2006' and '12/03/2006'

that should get you the date between those two time frames. Good luck.

-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

chillino SSC Rookie Points: 42 More actions · Answer 2

I hope I am not posting this twice ,since my answer seems to have disappeared but

Try this script to generate your dates

Generates a rowset with a datetime values for every day between a start datetime and end datetime.

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=398

I commented this line from the script --DROP TABLE #NumRange

and then created this lookup table with your dates

create table numrange (MyDates datetime)

insert into numrange select * from #NumRange

I did not create your data and test it since you did not provide a script but this

query should get your results

select convert(varchar,MyDates,1),null as StringCol from NumRange

where convert(varchar,MyDates,1)not in (select DateCol from MyDateColumnTable)

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Hi Najeeb,

Chillino is right - using a 'numbers' table makes this problem very easy to solve. Here's one way you can achieve what you need. Just run it to see...

--Prepare example data

SET DATEFORMAT 'dmy'

DECLARE @t TABLE (DateCol DATETIME, StringCol VARCHAR(10))

INSERT @t

SELECT '02/03/2006', 'Test1'

UNION ALL SELECT '03/03/2006', 'Test2'

UNION ALL SELECT '06/03/2006', 'Test3'

UNION ALL SELECT '07/03/2006', 'Test4'

UNION ALL SELECT '08/03/2006', 'Test5'

UNION ALL SELECT '12/03/2006', 'Test6'

--Inputs

DECLARE @DateFrom DATETIME

DECLARE @DateTo DATETIME

SET @DateFrom = '02/03/2006'

SET @DateTo = '12/03/2006'

--Create numbers table of required size

DECLARE @NumberOfDays INT

SET @NumberOfDays = DATEDIFF(d, @DateFrom, @DateTo) + 1

SET ROWCOUNT @NumberOfDays

DECLARE @Numbers TABLE (i INT IDENTITY(0, 1), j BIT)

INSERT @Numbers SELECT NULL FROM master.dbo.syscolumns a, master.dbo.syscolumns b

SET ROWCOUNT 0

--Put them together

SELECT DATEADD(d, i, @DateFrom), StringCol

FROM @Numbers n LEFT OUTER JOIN @t t ON DATEADD(d, n.i, @DateFrom) = t.DateCol

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

najeeb selk SSC Enthusiast Points: 103 More actions · Answer 4

Hi

thanks

I will get my really problem

so I work on program that calculate Employee's attendance

and I have

table have (Employee's name, In time, Out time)

that i just record the attendance's day

and table with (Employee's name, Vacation's Day)

so i want to get a report that have

(Date, state of day, Employee's name)

between two dates

Note: state of day must be (Attendance, Vacation)

Thanks