Date Format issues with update query. 1

[RobHat]

Hi guys,
I am not sure if this is the right forum but here it goes.

I have just gone from using Microsoft Access as the database behind the admin part of my site to Microsoft SQL 2005. The site is built with coldfusion and I am a complete novice with Microsoft SQL Server. Everything with the change over has gone relativly smoothly apart from a date format issue.

The site generates call lists for our telesales staff and the client information form they use has a next contact date on it. They put a date in which is then used later to pull up a call list for that particluar day. So basicly what I am trying to say is it needs to updateable and not just a one time entry.

I am in the UK and the date format I want to use is dd/mm/yyyy. I can enter the date in like this but when I click update form it changes it and inserts into the db as mm/dd/yyyy. But it only does this for dates that are able to be changed. ie if the date is entered as 02/01/2008 (dd/mm/yyyy) then it will be changed to 01/02/2008 (mm/dd/yyyy). If the date entered is 16/01/2008 it stays the same. The date column in my database is set to data type 'datetime' and I have tryed multiple data types in the update code of my site. Does anybody have any ideas where I am going wrong.

Any help or ideas on this will be greatly appreciated. As I stated before I am a complete novice with SQL Server and am probably missing something stupid.

Thanks in advance

Rob

 

[RobHat]

Incase It helps here is part of the code I am using:

<cfquery datasource="mydatasource">
UPDATE dbo.mytable
SET next_contact=<cfif IsDefined("FORM.nextcontact") AND #FORM.nextcontact# NEQ "">
<cfqueryparam value="#FORM.nextcontact#" cfsqltype="cf_sql_timestamp">
<cfelse>
NULL
</cfif>

The code for the FORM.nextcontact field is as follows:

<input name="nextcontact" type="text" id="nextcontact" value="<cfoutput>#LSDateFormat(clientDetails.next_contact,'DD/MM/YYYY')#</cfoutput>" />

 

[r937]

ie if the date is entered as 02/01/2008 (dd/mm/yyyy) then it will be changed to 01/02/2008 (mm/dd/yyyy).

always use year-month-day format for your date strings, and your problems will disappear as if by magic


so instead of entering '02/01/2008' you should make sure it hits the database as '2008-01-02'

by "hits the database" i mean you can either type the value into your user interface that way, or let your user type in dd/mm/yyyy but make sure to re-arrange it to yyyy-mm-dd before executing the UPDATE query

by the way, i hope your UPDATE query has a WHERE clause, otherwise it will change the next_contact value for all rows!!



r937.com | rudy.ca

 

[RobHat]

Thanks for the info, It does have a where clause it is based on the clients id number. So basicly as far the user sees I do it as dd/mm/yyyy and pass it to the db as yyyy/mm/dd. The only issue I see with this is when it comes to using this date later on the call log. I need to display the date as dd/mm/yyyy. the query I previously used with the access db was this:

<CFSET today = #createODBCdate(Now())#>
<cfquery name="today_log" datasource="mydatasource">
SELECT *
FROM dbo.mytable
WHERE next_contact = #today#
</cfquery>

Do I need to alter this to view the right date? I am guessing it would be in the cfset code??? or just where it displays it on the page as in:


<cfoutput>#LSDateFormat(clientDetails.next_contact,'DD/MM/YYYY')#</cfoutput>

Thanks for the help.

Rob

 

[RobHat]

Also where am I best to convert the date to yyyy/mm/dd? in the update query or elsewhere?

 

[r937]

re-format the date in coldfusion before submitting the query

notice how in your SELECT query you are using createODBCdate to provide the current date value -- guess what format this is when the database gets it ;-)

when retrieving a date from the database, if it doesn't come out by default in yyyy-mm-dd format, you can reformat it using the sql server CONVERT function with style 120 (this produces a string rather than a date)

r937.com | rudy.ca

 

[RobHat]

What would be the correct data type tp use for this? At present I am using cf_sql_timestamp is ok for what I need?

 

[RobHat]

Ok sorry forget the last question. I have since read an article that stated the data type in the query must match the columns data type. So I have changed cf_sql_timestamp to cf_sql_datetime.

 

[RobHat]

when I upsized my database from access. It put the 2000 records into my new sql table with the date in the format dd/mm/yyyy. is there a way to convert all of them together or can I use them as they are? so from now on I insert all dates as yyyy-mm-dd. but when it comes to retrieving the information will coldfusion look at the date format the data is already in and convert it if needs be but leave it if it is ok?

 

[r937]

let me assure you that the dates in sql server are not stored in any particular format

if you got them stored into your table, they're okay

(they are actually stored as integers, but you need not know the particulars)

the only places where formats come into play is when you are inserting dates, and when you are retrieving dates

if you use yyyy-mm-dd format, you should have no difficulties inserting a date, as you might with 02/01/2008 or 01/02/2008

which leaves retrieval

when you select a date column, sql server may hand it to you in some other format (mm/dd/yyyy is common)

if it doesn't come out by default in yyyy-mm-dd format, you can reformat it using the sql server CONVERT function with style 120 (this produces a string rather than a date)

r937.com | rudy.ca

 

[RobHat]

Thats cool thanks for the help. It's most appreciated.

Regards

Rob