Update a date-Time field

Databases
1

Hi
I have thousands of records in a table in MYSQL and one of the fields is showing the date and time in this format: YYYY-MM-DD HH:MM:SS

Is there a way I can change the time to 00:00:00 leaving the date as it is, without have to reimport all these records again?

The reason I want this is so I can have a search by date field on a form using coldfusion, its not searching correctly for me when theres a time present

Thanks

2

i would leave the data as is and fix your search query

3

I wouldn’t set all times to 00:00:00 since that means you’ll loose information/meaning.
What you could do is:


SELECT * FROM some_table WHERE CAST(some_date_field AS DATE)='2010-04-23';

Thus ignoring the time part through a cast.
However, if the table to be searched has a lot of records this might become quite slow, I which case I would suggest splitting the DATETIME field to a DATE and TIME field, so you can search the DATE field separately from the TIME.

A third method would be to use timestamps instead of DATETIME, DATE and/or TIME, and then convert user input to timestamps to create a range. If you also create an index on the timestamp field, IMHO, that would be the the solution to yield results the fasts (as I think timestamp comparison works faster than DATE comparison).

4

Hi ScallioXTX

I’ll never use the time part of this field, im only using the date part throughout coldfusion
so if it could be deleted altogether it would be easier

I tested it by manually changing the date fields to:
for example 2010-04-23 00:00:00
and it was able to search it fine with no problems but when I put the time back in theres where the problems started

I have a bit of code in coldfusion which im using when importing the new records and its filling in the time like this: 00:00:00 but as for all the old records im kind of stuck as it will take quite a while to import all these thousands of records again

Hope this makes sense!

5

the correct approach is to fix the query, not the data

separate DATE and TIME columns only makes things worse

converting to unix epoch times is also less than optimal

just fix the query

(i keep saying “fix the query” without actually showing the SQL syntax, because i haven’t seen the query yet so i can’t comment on it)

6

heres the Query im using in coldfusion


<cfquery name="qget" datasource="#TEST.dsn#">
select I.COMPANY_Number,INVOICE_Number, C.COMPANY_Name, INVOICE_Key, I.CUSTOMER_AccNum, I.INVOICE_DateAdded
from INVOICE I, COMPANY C
where I.COMPANY_Number = C.COMPANY_Number
AND I.COMPANY_Number = #form.COMPANY_Number#
AND I.INVOICE_Number LIKE '%#form.INVOICE_Number#%'
AND CUSTOMER_AccNum LIKE '%#form.CUSTOMER_AccNum#%'
<cfif isdefined ('form.DateFrom')>
<cfif form.DateFrom neq ''>		
AND I.INVOICE_DateAdded >= '#dateformat(form.DateFrom,"yy-mm-dd")#'
AND I.INVOICE_DateAdded <= '#dateformat(form.DateTo,"yy-mm-dd")#'
</cfif>
</cfif>
7

well, there’s two ways you can go

first, if you’re serious that you won’t ever need the time part, then you should change the table and use a DATE column instead of a DATETIME column

however, if you want to leave your DATETIME column as is (and i might, if it were me, even if i didn’t think i needed the time part)…

… then your CF query should do two things –

first, make sure you cover yourself for SQL injection

second, make sure the date range works like this –


AND I.INVOICE_DateAdded >= '#dateformat(form.DateFrom,"yy-mm-dd")#'
AND I.INVOICE_DateAdded  < '#dateformat(form.DateTo,"yy-mm-dd")#'
                                   + INTERVAL 1 DAY

the reason you add 1 day is to pick up all the times on the last day… note the “less than” operator, rather than “less than or equal”

8

Thanks r937

I deleted it altogether, now I have another database but its using SQL Enterprise Manager 2000, is there a way I can change the Datetime field in this also? It doesnt accept “Date”
Thanks

9

SQL Server has DATETIME only, until SQL Server 2008, when DATE is added

there’s no problem using DATETIME as long as you make sure you insert values with a zero time component, i.e. don’t use GetDate()

10

Ah right, thats great

Thanks for your help