Re: Not so simple date format question...

[Jerry Barnes]

You could also convert the date to a new format in a view.


~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333181
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Not so simple date format question...

[Che Vilnonis]

Barney, thanks! I had something similar.
Leigh, that is what I was looking for. Works perfectly. Thanks to you as
well.

Che 

-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com] 
Sent: Tuesday, April 27, 2010 11:44 AM
To: cf-talk
Subject: RE: Not so simple date format question...

Yes, that is one way to do it. If you think you will be doing it frequently,
you could create a "view" (containing the convert) and call the view in your
CF code:

SELECT convert(datetime, YourVarcharCol, 112) AS ConvertedToDateTime 

http://msdn.microsoft.com/en-us/library/ms187928.aspx



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333159
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Not so simple date format question...

[Jake Churchill]

Just do something like this:




#DateFormat(CreateDate(MID(str,1,4),MID(str,5,2),MID(str,7,2)),'mm/dd/')#


On Tue, Apr 27, 2010 at 10:32 AM, Che Vilnonis  wrote:

>
> I'm working with an accounting database that stores order dates as a
> varchar
> (8) field. An example would be "20100420".
> Obviously, CF's dateformat chokes when used.
>
> Short of using a bunch of cfset statements with CF functions, how can I (in
> SQL preferrably or CF) easily format this string to "04/20/2010"?
>
> Thanks, Che
>
>
>
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333158
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Not so simple date format question...

[Leigh]

> MSSQL 2005
> I figured a db convert statement might be needed? 

Yes, that is one way to do it. If you think you will be doing it frequently, 
you could create a "view" (containing the convert) and call the view in your CF 
code:

SELECT convert(datetime, YourVarcharCol, 112) AS ConvertedToDateTime


http://msdn.microsoft.com/en-us/library/ms187928.aspx



  

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333156
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Not so simple date format question...

[Che Vilnonis]

MSSQL 2005
I figured a db convert statement might be needed? 

-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com] 
Sent: Tuesday, April 27, 2010 11:37 AM
To: cf-talk
Subject: Re: Not so simple date format question...


Which database are you using? 


  



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333155
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Not so simple date format question...

[Barney Boisvert]

s = "20100420";
mid(s, 5, 2) & "/" & right(s, 2) & "/" & left(s, 4);

cheers,
barneyb

On Tue, Apr 27, 2010 at 8:32 AM, Che Vilnonis  wrote:
>
> I'm working with an accounting database that stores order dates as a varchar
> (8) field. An example would be "20100420".
> Obviously, CF's dateformat chokes when used.
>
> Short of using a bunch of cfset statements with CF functions, how can I (in
> SQL preferrably or CF) easily format this string to "04/20/2010"?
>
> Thanks, Che
>
>
>
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333154
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Not so simple date format question...

[Leigh]

Which database are you using? 


  

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333153
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Not so simple date format question...

[Che Vilnonis]

I'm working with an accounting database that stores order dates as a varchar
(8) field. An example would be "20100420".
Obviously, CF's dateformat chokes when used. 

Short of using a bunch of cfset statements with CF functions, how can I (in
SQL preferrably or CF) easily format this string to "04/20/2010"?

Thanks, Che




~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333152
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Date format question

[gary gilbert]

I would say that the client who is using your api is not using the
datetime.parse method in .net which allows you to add a "culture" specific
format when a string date is parsed.

the .NET parse method is an overloaded .NET method that can either take the
following forms.

Parse(String) 
Parse(String, 
IFormatProvider)
Parse(String, IFormatProvider,
DateTimeStyles)

Regards

Gary Gilbert
http://www.garyrgilbert.com/blog


On Tue, Jan 5, 2010 at 11:14 AM, Stefan Richter wrote:

>
> My CF app stores dates in a MySQL database which I often submit simply
> using NOW() in my SQL statement. The resulting database entry has this
> format:
> 2008-01-29 11:08:40
> Other times I am using a default MYSQL value of CURRENT_TIMESTAMP in a
> timestamp field, which results in the same format.
>
> I have a public API which returns the date in the same format. Now one of
> my clients told me this:
> ".Net functions do not recognize the date/time strings that the API returns
> making all date/time’s invalid."
>
> Is that really the case? Am I doing something wrong, or is he?
>
> Regards,
>
> Stefan
>
>
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329413
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Date format question

[John M Bliss]

Perhaps suggest ParseExact to him:
http://www.codeproject.com/KB/cs/String2DateTime.aspx

On Tue, Jan 5, 2010 at 4:14 AM, Stefan Richter wrote:

>
> My CF app stores dates in a MySQL database which I often submit simply
> using NOW() in my SQL statement. The resulting database entry has this
> format:
> 2008-01-29 11:08:40
> Other times I am using a default MYSQL value of CURRENT_TIMESTAMP in a
> timestamp field, which results in the same format.
>
> I have a public API which returns the date in the same format. Now one of
> my clients told me this:
> ".Net functions do not recognize the date/time strings that the API returns
> making all date/time’s invalid."
>
> Is that really the case? Am I doing something wrong, or is he?
>
> Regards,
>
> Stefan
>
>
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329412
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Date format question

[Stefan Richter]

My CF app stores dates in a MySQL database which I often submit simply using 
NOW() in my SQL statement. The resulting database entry has this format:
2008-01-29 11:08:40
Other times I am using a default MYSQL value of CURRENT_TIMESTAMP in a 
timestamp field, which results in the same format. 

I have a public API which returns the date in the same format. Now one of my 
clients told me this:
".Net functions do not recognize the date/time strings that the API returns 
making all date/time’s invalid."

Is that really the case? Am I doing something wrong, or is he?

Regards,

Stefan



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329410
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Coldfusion Date format question

[fun and learning]

Hi All,

Thanks a lot for the answers. It works now...

Thanks


> Using cfsqltype="cf_sql_timestamp" will definitely solve that problem.
> 
> 
> I expect you're just trying to give an example, but if all you're
> trying to do is move values from one table to another, you can do 
> this
> en-mass with SQL:
> 
> 
> insert into table2(date_field2)
> select date_field1 from table1
> 
> 
> Cheers,
> Kris
> 
> 
> > I have an issue inserting date and time together into a database 
> table from coldfusion.
> >
> > Right now, I am using  to get records from a particular 
> table, in which one of the field is date, and its value in the 
> database is something like
> > "2/14/2007 9:10:12 AM". I am performing insert operation into 
> another table which has to insert this date field into the column of 
> new table. I am doing currently as follow:
> >
> > 
> >   select date_field from table
> > 
> >
> > 
> >  
> >    insert into table2 (date_field1) values ( cfsqltype="cf_sql_date" value=#getDate.date_field#>)
> >  
> > 
> >
> > The problem with this is only the date is getting inserted but not 
> the time. How to format 


~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322437
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Coldfusion Date format question

[Kris Jones]

Using cfsqltype="cf_sql_timestamp" will definitely solve that problem.

I expect you're just trying to give an example, but if all you're
trying to do is move values from one table to another, you can do this
en-mass with SQL:


insert into table2(date_field2)
select date_field1 from table1


Cheers,
Kris


> I have an issue inserting date and time together into a database table from 
> coldfusion.
>
> Right now, I am using  to get records from a particular table, in 
> which one of the field is date, and its value in the database is something 
> like
> "2/14/2007 9:10:12 AM". I am performing insert operation into another table 
> which has to insert this date field into the column of new table. I am doing 
> currently as follow:
>
> 
>   select date_field from table
> 
>
> 
>  
>    insert into table2 (date_field1) values ( cfsqltype="cf_sql_date" value=#getDate.date_field#>)
>  
> 
>
> The problem with this is only the date is getting inserted but not the time. 
> How to format 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322384
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Coldfusion Date format question

[Dan Baughman]

I believe the issue may be with your cfqueryparam tag.

You are using cf_sql_date, which to me implies only a date, not a time.
According to livedocs the CF_SQL_TIMESTAMP cfqueryparam is what maps to the
Microsoft SQL datetime type.

Dan

On Mon, May 11, 2009 at 1:37 PM, fun and learning
wrote:

>
> Hello All,
>
> I have an issue inserting date and time together into a database table from
> coldfusion.
>
> Right now, I am using  to get records from a particular table, in
> which one of the field is date, and its value in the database is something
> like
> "2/14/2007 9:10:12 AM". I am performing insert operation into another table
> which has to insert this date field into the column of new table. I am doing
> currently as follow:
>
> 
>   select date_field from table
> 
>
> 
>  
>insert into table2 (date_field1) values ( cfsqltype="cf_sql_date" value=#getDate.date_field#>)
>  
> 
>
> The problem with this is only the date is getting inserted but not the
> time. How to format this.
>
> It would be great if somebody can reply, I am not sure if my messages are
> getting posted, as I posted couple of issues I had some time back, but never
> got any reply back.
>
> Thanks.
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322380
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Coldfusion Date format question

[Matt Quackenbush]

You're telling CF to only insert the date (cf_sql_date).  Assuming that your
database column is setup to hold a timestamp (e.g. a data type of datetime,
timestamp or similar), try the following:




~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322379
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Coldfusion Date format question

[John M Bliss]

Use cfsqltype="CF_SQL_TIMESTAMP" and confirm that table2.date_field1 is of
type datetime.

On Mon, May 11, 2009 at 2:37 PM, fun and learning
wrote:

>
> Hello All,
>
> I have an issue inserting date and time together into a database table from
> coldfusion.
>
> Right now, I am using  to get records from a particular table, in
> which one of the field is date, and its value in the database is something
> like
> "2/14/2007 9:10:12 AM". I am performing insert operation into another table
> which has to insert this date field into the column of new table. I am doing
> currently as follow:
>
> 
>   select date_field from table
> 
>
> 
>  
>insert into table2 (date_field1) values ( cfsqltype="cf_sql_date" value=#getDate.date_field#>)
>  
> 
>
> The problem with this is only the date is getting inserted but not the
> time. How to format this.
>
> It would be great if somebody can reply, I am not sure if my messages are
> getting posted, as I posted couple of issues I had some time back, but never
> got any reply back.
>
> Thanks.
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322378
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Coldfusion Date format question

[Nathan Strutz]

For your cfqueryparam, cf_sql_date will insert literally only the date into
the database - instead, use cf_sql_timestamp.

nathan strutz
[Blog and Family @ http://www.dopefly.com/]
[AZCFUG Manager @ http://www.azcfug.org/]



On Mon, May 11, 2009 at 12:37 PM, fun and learning
wrote:

>
> Hello All,
>
> I have an issue inserting date and time together into a database table from
> coldfusion.
>
> Right now, I am using  to get records from a particular table, in
> which one of the field is date, and its value in the database is something
> like
> "2/14/2007 9:10:12 AM". I am performing insert operation into another table
> which has to insert this date field into the column of new table. I am doing
> currently as follow:
>
> 
>   select date_field from table
> 
>
> 
>  
>insert into table2 (date_field1) values ( cfsqltype="cf_sql_date" value=#getDate.date_field#>)
>  
> 
>
> The problem with this is only the date is getting inserted but not the
> time. How to format this.
>
> It would be great if somebody can reply, I am not sure if my messages are
> getting posted, as I posted couple of issues I had some time back, but never
> got any reply back.
>
> Thanks.
>
> 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322377
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Coldfusion Date format question

[fun and learning]

Hello All,

I have an issue inserting date and time together into a database table from 
coldfusion.

Right now, I am using  to get records from a particular table, in 
which one of the field is date, and its value in the database is something like 
"2/14/2007 9:10:12 AM". I am performing insert operation into another table 
which has to insert this date field into the column of new table. I am doing 
currently as follow:


   select date_field from table



  
insert into table2 (date_field1) values ()
  


The problem with this is only the date is getting inserted but not the time. 
How to format this. 

It would be great if somebody can reply, I am not sure if my messages are 
getting posted, as I posted couple of issues I had some time back, but never 
got any reply back.

Thanks. 

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322376
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: Date Format Question

[Taco Fleur]

what about

 
"all")>
 
listLast(myDate, "/"))>
 
 #dateFormat(myDate, "dd/mm/yy")# #timeFormat(myTime, "hh:mm tt")#

Taco Fleur
Blog  
http://www.tacofleur.com/index/blog/
Methodology http://www.tacofleur.com/index/methodology/

Tell me and I will forget
Show me and I will remember
Teach me and I will learn 

-Original Message-
From: Jeff Fongemie [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 18 February 2004 2:29 AM
To: CF-Talk
Subject: Date Format Question

I've got a date-time that is goinmg to be passed to me as this:

2003:12:27 12:19:58

Can anyone suggest the best way to get that into a more friendly format as
in.. 

12/27/03 12:19pm ??

It obviously won't fit into standard dateformat function. Any ideas I can
come up with are complicated. There must be an easy solution?

Jeff F 
  _
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: Date Format Question

[Ben Doom]

It changes the colons to hypens.

--Ben Doom

Jeff Fongemie wrote:

> Am I missing something?
> 
> I've got:
> 
> 
> 
> Original Date:#REReplace(mydate, "^([0-9]*):([0-9]*):(.*)$","\1-\2-\3")#
> 
> This gives me exactly the same date time format as I input? The regex 
> does not change anything?
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: Date Format Question

[Jochem van Dieten]

Jeff Fongemie said:
> I've got a date-time that is goinmg to be passed to me as this:
>
> 2003:12:27 12:19:58
>
> Can anyone suggest the best way to get that into a more friendly
> format as in..
>
> 12/27/03 12:19pm ??
>
> It obviously won't fit into standard dateformat function. Any ideas
> I can come up with are complicated. There must be an easy solution?

theDate = REReplace(input, "^([0-9]*):([0-9]*):(.*)$","\1-\2-\3");

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: Date Format Question

[Barney Boisvert]

Date = parseDateTime(Rereplace(date, "^([0-9]{4}):([0-9]{2}):(.*)$",
"\1/\2/\3"));
Date = dateFormat(date, "...");

You might be able to get by with removing either parseDateTime() or the
Rereplace(), but I'm not 100% sure.

Cheers,
barneyb

> -Original Message-
> From: Jeff Fongemie [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, February 17, 2004 8:29 AM
> To: CF-Talk
> Subject: Date Format Question
> 
> I've got a date-time that is goinmg to be passed to me as this:
> 
> 2003:12:27 12:19:58
> 
> Can anyone suggest the best way to get that into a more 
> friendly format as in.. 
> 
> 12/27/03 12:19pm ??
> 
> It obviously won't fit into standard dateformat function. Any 
> ideas I can come up with are complicated. There must be an 
> easy solution?
> 
> Jeff F
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Date Format Question

[Jeff Fongemie]

I've got a date-time that is goinmg to be passed to me as this:

2003:12:27 12:19:58

Can anyone suggest the best way to get that into a more friendly format as in.. 

12/27/03 12:19pm ??

It obviously won't fit into standard dateformat function. Any ideas I can come up with are complicated. There must be an easy solution?

Jeff F
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]