Any chance the back end is SQL Server?Need help.
I have dates like 11-Apr-2022 22:29:00 in date field of a table.
Need query to change it to 11-Apr-2022 00:00:00.
Another example
01-Nov-2022 06:50:00 to 01-Nov-2022 00:00:00
Thank you
To clarify, if you enter a date without a time, the time element is .0When you enter the date it also captures and stores the time component
Yes and it is fast. There might be faster solution out there but I did it on a table of 1.5M rows relatively quickly. I don't remember how many seconds.So you convert it twice? Once to a string, then back to a date?
If you use DatePart(), you avoid the conversion step. Since DatePart() returns a date data type. Technically using a string expression such as Format() solves the problem but I prefer to not change datatype when it is not necessary.
You got it exactly right. Thank you very much.To clarify. The Date data type ALWAYS includes a time component because dates are stored as double precision numbers. The integer portion represents the number of days since Dec 30, 1899 and the decimal represents the fraction of a day. So noon on Dec 31, 1899 = 1.5. Noon on Dec 29, 1899 = -1.5 . midnight on June 2, 2023 = 45079.0 and that's what a date without a time actually looks like internally. Other RDBMS' use the same technique but might use a different origin date. I think SQL server uses Jan 1, 1900. The point of storing the date this way is to eliminate conversions when doing date arithmetic.
June 2, 2023 12:39 = 45079.5274074074
If you do not need time in the table, then the best option is simply not to store it. Use Date() to store just a date. Use Now() to store the current date and the time of day. If you want to get rid of the time components use an update query (don't forget to back up first) to replace the date using the DateValue() function rather than doing this every time you need to work with the data.
Formatting a Date field using ShortDate simply hides the time component. It is still in the table data and it will affect sorting and compares. So you might find that June 2, 2023 does not = June 2, 2023 because the stored data value includes time but you are just hiding it by using formatting.
Many thanks. Greatly appreciatedKind of piggybacking on that, it is also a VERY good idea and well advised to spend some time thinking about the downstream (if there's any 'downstream' left at this point in the process.....or if there might be). Because......Sorting.
Yes. Great help. Thank youKind of piggybacking on that, it is also a VERY good idea and well advised to spend some time thinking about the downstream (if there's any 'downstream' left at this point in the process.....or if there might be). Because......Sorting.
Thank you very much. Good answerSilva is asking for a query to remove time. In MS-SQL there a covert function. But in Access I use:
update [tablename] set datefield = format("yyyy-mm-dd", datefield) where datefield condition criteria
Format is designed to output text sting but if your datefield is already of type datetime there will be an implicit conversion. And the format yyyy-mm-dd is a standard database date format that will avoid errors.
You could use the same query to pull the time only using format "hh:nn:as".
Spot on. Thank you very muchTo clarify, if you enter a date without a time, the time element is .0
if you are using the now() function it will store the time at that point but use the date() function instead and the time will be 0
if you are receiving data from another system that includes a time then use the datevalue function to replace the time part with .0
Excellent answer. Thank youUse DateValue(), either to update your field or just retrieve the date portion.
UPDATE YourTable
SET DateField = Int(DateField)
;
UPDATE YourTable
SET DateField = DateValue(DateField);