Update Query To Change DD-MMM-YYYY HH:mm:ss To DD-MMM-YYYY 00:00:00 (1 Viewer)

[Selva7]

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

 

[GaP42]

Do you ever need the time component? Your table has a date field, which includes the time component. When you enter the date it also captures and stores the time component. The presentation of your date depends on format, but it always has the time as well, so even if you have defined the format of the date in the table as short date, it still has the time component.

If the date field on your table is short date, you can use DATE() as the default to capture the current date. Now() captures the date-time. Use Date() in your vba to get write the date with time expressed as 00:00:00

If this is not viable, or you need to retain the actual time as entered/stored, then you will need to use a DatePart function in your query to get the date, without time, and append 00:00:00 to that string - but that does not itself change the value in the table.

https://support.microsoft.com/en-us...f4-91df-44d8-a386-a3c43f0ac99e#__toc316307576

 

[Gasman]

Use DateValue(), either to update your field or just retrieve the date portion.

 

[Pat Hartman]

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.

 

[Isaac]

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

Any chance the back end is SQL Server?

Convert() makes it all pretty easy...much easier than Access.

 

[CJ_London]

When you enter the date it also captures and stores the time component

To 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

 

[JeanMarc22]

Silva 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".

 

[Gasman]

So you convert it twice? Once to a string, then back to a date?

 

[JeanMarc22]

So you convert it twice? Once to a string, then back to a date?

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.

 

[Pat Hartman]

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.

 

[Isaac]

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.

Kind 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.

 

[Selva7]

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.

You got it exactly right. Thank you very much.

 

[Selva7]

Kind 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.

Many thanks. Greatly appreciated

 

[Selva7]

Kind 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 you

 

[Selva7]

Silva 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".

Thank you very much. Good answer

 

[Selva7]

To 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

Spot on. Thank you very much

 

[Selva7]

Use DateValue(), either to update your field or just retrieve the date portion.

Excellent answer. Thank you

 

[cheekybuddha]

Another variation:

UPDATE YourTable
  SET DateField = Int(DateField)
;

 

[Pat Hartman]

I would suggest using a date function when working with date fields.

UPDATE YourTable
    SET DateField = DateValue(DateField);