To fetch records between two dates (today and yesterday) considering day andTime

Question

I have three columns named docDt , docDateTime and docTime .

Where as docDt contains only date part(eg. 2017-01-01 00:00:00:000) and docTime contains only time(eg. 1900-01-01 22:00:00:000)

Also i am storing combined docDt and docTime to docDateTime (ie. 2017-01-01 22:00:00:000)

I'm inserting new record to table dated docDt(2017-01-02 00:00:00:000) and docTime(1900-01-01 10:00:00:000) and also storing combined date and time(2017-01-02 10:00:00:000) docDateTime .

Now I need to fetch records greater than the mentioned 2017-01-01 22:00:00:000

I have tried using the following condition

CONVERT(varchar(8), STH.DocTime , 108) >= '22:00:00.000' and CONVERT(varchar(10),  STH.DocDt , 120) >='2017-01-01' ORDER BY STH.DocDt

NOTE: for docTime by default date is 1900-01-01

EDIT: docDateTime only for reference we can't use that because it will be stored in mysql. where as doctime and docdt are stored in sqlserver

May I get any help.

you are not highlighting the issue with your try, what are you getting when you compare with docDateTime? — Anil, Jan 05 '17 at 06:08
your docTime column data type might be the Datetime.so when you are trying to insert only time by default it appends date **1900-01-01** to your time.try to change the datatype format as time or else you should insert with date.for your reference check this query. `create table #tab (time_date datetime) insert into #tab values('22:44:36') create table #tab1 (time_date time) insert into #tab1 values('22:44:36') select * from #tab select * from #tab1` — Mahesh.K, Jan 05 '17 at 06:08
@AnilKumar need to get latest records after 2017-01-01 22:00:00:000 — light_ray, Jan 05 '17 at 06:10
Trying to understand the relevance of docDateTime column mentioned in the question, when we can not use this. — Anil, Jan 05 '17 at 06:13
you can look for approach in so http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server — Anil, Jan 05 '17 at 06:14

Abdul Rasheed · Accepted Answer · 2017-01-05T08:07:18.237

1

If your time part of the date column docDt is always zero and date part of the time column docTime is also always base date (1900-01-01), then you can simply add this,

SELECT *
FROM   TABLE
WHERE  docDt + docTime > '2017-01-01 22:00:00:000'

edited Jan 05 '17 at 08:07

answered Jan 05 '17 at 06:20

Abdul Rasheed

6,486
4
32
48

Ranjana Ghimire · Answer 2 · 2017-01-05T06:12:46.763

0

Just compare the dates: (It works completely fine):

DECLARE @DATE TABLE (docDt  DATETIME,docTime DATETIME,docDateTime DATETIME )
INSERT INTO @DATE VALUES('2017-01-01 00:00:00:000','1900-01-01 22:00:00:000','2017-01-01 22:00:00:000')
INSERT INTO @DATE VALUES('2017-01-02 00:00:00:000','1900-01-01 10:00:00:000','2017-01-02 10:00:00:000')

SELECT * FROM @DATE WHERE docDateTime>'2017-01-01 22:00:00:000'

-- or ( as per your requirement)

select * from @DATE where (CONVERT(VARCHAR(10), docDt, 120)+' '+CONVERT(VARCHAR(10), docTime, 108))>'2017-01-01 22:00:00:000'

result:

------------------------------------------------------------------------
|2017-01-02 00:00:00.000|1900-01-01 10:00:00.000|2017-01-02 10:00:00.000|
-------------------------------------------------------------------------

edited Jan 05 '17 at 06:12

answered Jan 05 '17 at 06:02

Ranjana Ghimire

1,785
1
12
20

thanks, i need to fetch date from docDt column and time from docTime., I won't get combined records like this '2017-01-01 22:00:00:000'. – light_ray Jan 05 '17 at 06:05
@user4246662, but you are mentioned , you are storing combined docDt and docTime to docDateTime. – Abdul Rasheed Jan 05 '17 at 06:10
we have use only docdt and doctime – light_ray Jan 05 '17 at 06:11

score -1 · Answer 3 · answered Jan 05 '17 at 06:01

-1

Try with docDateTime

 docDateTime >2017-01-01 22:00:00:000

answered Jan 05 '17 at 06:01

Mahesh.K

901
6
15

Thanks, records are stored in different columns – light_ray Jan 05 '17 at 06:03

To fetch records between two dates (today and yesterday) considering day andTime

3 Answers3