Select records between day, time 05:00:00 and day+1 until time 05:00:00
-
-
Hello everyone, Can someone help me select rows between 2 dates en between 2 times. I need to select all records from 02/09/2014 starting from time 05:00:00 until the next day time until 05:00 If I do it like this then it wont work, no records shows:
SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014' and '03/09/2014'
AND Convert(Time,TIME_CREATED) between '05:00:00' AND '05:00:00'It should be something like this: Between DATE_CREATED 02/09/2014, TIME_CREATED 05:00:00 and DATE_CREATED 03/09/2014, TIME_CREATED 05:00:00 Kind regards, Ambertje
As others have said, the time should be part of the
DATE_CREATEDcolumn. However, it's still possible to get what you need:WHERE
(DATE_CREATED = '20140902' And Convert(time, TIME_CREATED) >= '05:00:00')
Or
(DATE_CREATED = '20140903' And Convert(time, TIME_CREATED) <= '05:00:00')
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
As others have said, the time should be part of the
DATE_CREATEDcolumn. However, it's still possible to get what you need:WHERE
(DATE_CREATED = '20140902' And Convert(time, TIME_CREATED) >= '05:00:00')
Or
(DATE_CREATED = '20140903' And Convert(time, TIME_CREATED) <= '05:00:00')
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
As others have said, the time should be part of the
DATE_CREATEDcolumn. However, it's still possible to get what you need:WHERE
(DATE_CREATED = '20140902' And Convert(time, TIME_CREATED) >= '05:00:00')
Or
(DATE_CREATED = '20140903' And Convert(time, TIME_CREATED) <= '05:00:00')
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
That is great! I get started down a path and do not think outside of that. :~
-
Yes, kinda predictable. It's a bad idea to do so, and should be fixed. The time you are saving is a culture-specific format, it is a text, something the computer does not calculate with. A DateTime in a computer is a floating point. The integer-part counts the days passed since the epoch (start of counting of days, often 1/1/1900), the decimal part represents the time, in ticks. They are not two separate facts - and should be modelled as a single field, of the DateTime-datatype. The computer can easily calculate with those. Breaking the date and time into separate fields is as usefull as using a separate field for the day, month, year, hour, minute and second. If they represent a single atomic fact, than that is how it should be modelled.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Yes, kinda predictable. It's a bad idea to do so, and should be fixed. The time you are saving is a culture-specific format, it is a text, something the computer does not calculate with. A DateTime in a computer is a floating point. The integer-part counts the days passed since the epoch (start of counting of days, often 1/1/1900), the decimal part represents the time, in ticks. They are not two separate facts - and should be modelled as a single field, of the DateTime-datatype. The computer can easily calculate with those. Breaking the date and time into separate fields is as usefull as using a separate field for the day, month, year, hour, minute and second. If they represent a single atomic fact, than that is how it should be modelled.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
It's interesting that the Date type in Oracle while handled as a single entity but is stored internally as seven bytes. One byte each for year, month, day, hour minute, second and fraction of a second. It's a space waster, but oh so fast to calculate with. Timestamp on the other hand is stored as a floating point to save space.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
It's interesting that the Date type in Oracle while handled as a single entity but is stored internally as seven bytes. One byte each for year, month, day, hour minute, second and fraction of a second. It's a space waster, but oh so fast to calculate with. Timestamp on the other hand is stored as a floating point to save space.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Indeed.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Yes, kinda predictable. It's a bad idea to do so, and should be fixed. The time you are saving is a culture-specific format, it is a text, something the computer does not calculate with. A DateTime in a computer is a floating point. The integer-part counts the days passed since the epoch (start of counting of days, often 1/1/1900), the decimal part represents the time, in ticks. They are not two separate facts - and should be modelled as a single field, of the DateTime-datatype. The computer can easily calculate with those. Breaking the date and time into separate fields is as usefull as using a separate field for the day, month, year, hour, minute and second. If they represent a single atomic fact, than that is how it should be modelled.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
If date and time should not be keep separate, why did MS create data formats DATE and TIME? :-D
-
If date and time should not be keep separate, why did MS create data formats DATE and TIME? :-D
Because there are times when you do need to store just a date or just a time. The OP's example just isn't one of them. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
As others have said, the time should be part of the
DATE_CREATEDcolumn. However, it's still possible to get what you need:WHERE
(DATE_CREATED = '20140902' And Convert(time, TIME_CREATED) >= '05:00:00')
Or
(DATE_CREATED = '20140903' And Convert(time, TIME_CREATED) <= '05:00:00')
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
convert function is not working in access
-
Hello everyone, Can someone help me select rows between 2 dates en between 2 times. I need to select all records from 02/09/2014 starting from time 05:00:00 until the next day time until 05:00 If I do it like this then it wont work, no records shows:
SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014' and '03/09/2014'
AND Convert(Time,TIME_CREATED) between '05:00:00' AND '05:00:00'It should be something like this: Between DATE_CREATED 02/09/2014, TIME_CREATED 05:00:00 and DATE_CREATED 03/09/2014, TIME_CREATED 05:00:00 Kind regards, Ambertje
select * from Staging.[dbo].AD where DATE_CREATED between '2013-03-08 05:00:00' and '2013-03-09 05:00:00' -- I hope i will help u...
