>- see footer for list info -< DECLARE @date_in datetime SET @date_in='2006-06-28 08:00:00.0'
select CONVERT(VARCHAR(8), @date_in, 108) AS the_time -- results in: 08:00:00 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 22 July 2008 01:00 To: [email protected] Subject: Dev Digest, Vol 47, Issue 8 Send Dev mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit http://list.cfdeveloper.co.uk/mailman/listinfo/dev or, via email, send a message with subject or body 'help' to [EMAIL PROTECTED] You can reach the person managing the list at [EMAIL PROTECTED] When replying, please edit your Subject line so it is more specific than "Re: Contents of Dev digest..." Today's Topics: 1. RE: extracting time part from dates (Snake) 2. RE: RE: extracting time part from dates (Adrian Lynch) 3. RE: RE: extracting time part from dates (Snake) 4. Re: RE: extracting time part from dates (Peter Boughton) 5. Re: RE: extracting time part from dates (Steve Powell) ---------------------------------------------------------------------- Message: 1 Date: Mon, 21 Jul 2008 11:34:33 +0100 From: "Snake" <[EMAIL PROTECTED]> Subject: [CF-Dev] RE: extracting time part from dates To: "'Coldfusion Development'" <[email protected]> Message-ID: <[EMAIL PROTECTED]@snakepit.net> Content-Type: text/plain; charset="us-ascii" OK all I want to do is extract the time from a datetime field in the database (SQL server), but it is stripping 00 and converting to 0. E.G. Original column: 2006-06-28 08:00:00.0 Converts to : 8:0 I am currently using select slotid,dayofshow,forall,alldesc,starttime,endtime, datename(hh,starttime) + ':' + datename(mi,starttime) as starttime2, datename(hh,endtime) + ':' + datename(mi,endtime) as endtime2 from slots I have also tried using DatePart() with the same result. Surely these functions should not do this ? And no I cannot use CF to format it, it has to be in the right format in the query. -- Russ Michaels Certified ColdFusion Professional My Blog: www.russ.michaels.me.uk Commerical ColdFusion & BlueDragon Hosting: www.cfmxhosting.co.uk FREE CFML Developer hosting : www.cfdeveloper.co.uk ------------------------------ Message: 2 Date: Mon, 21 Jul 2008 14:19:11 +0100 From: "Adrian Lynch" <[EMAIL PROTECTED]> Subject: RE: [CF-Dev] RE: extracting time part from dates To: "Coldfusion Development" <[email protected]> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" Does swapping 'mi' to 'nn' hold onto the 0? Adrian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Snake Sent: 21 July 2008 11:35 To: 'Coldfusion Development' Subject: [CF-Dev] RE: extracting time part from dates >- see footer for list info -< OK all I want to do is extract the time from a datetime field in the database (SQL server), but it is stripping 00 and converting to 0. E.G. Original column: 2006-06-28 08:00:00.0 Converts to : 8:0 I am currently using select slotid,dayofshow,forall,alldesc,starttime,endtime, datename(hh,starttime) + ':' + datename(mi,starttime) as starttime2, datename(hh,endtime) + ':' + datename(mi,endtime) as endtime2 from slots I have also tried using DatePart() with the same result. Surely these functions should not do this ? And no I cannot use CF to format it, it has to be in the right format in the query. - Russ Michaels Certified ColdFusion Professional My Blog: www.russ.michaels.me.uk ------------------------------ Message: 3 Date: Mon, 21 Jul 2008 14:29:20 +0100 From: "Snake" <[EMAIL PROTECTED]> Subject: RE: [CF-Dev] RE: extracting time part from dates To: "'Coldfusion Development'" <[email protected]> Message-ID: <[EMAIL PROTECTED]@snakepit.net> Content-Type: text/plain; charset="us-ascii" Alas no -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Lynch Sent: 21 July 2008 14:19 To: Coldfusion Development Subject: RE: [CF-Dev] RE: extracting time part from dates >- see footer for list info -< Does swapping 'mi' to 'nn' hold onto the 0? Adrian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Snake Sent: 21 July 2008 11:35 To: 'Coldfusion Development' Subject: [CF-Dev] RE: extracting time part from dates >- see footer for list info -< OK all I want to do is extract the time from a datetime field in the database (SQL server), but it is stripping 00 and converting to 0. E.G. Original column: 2006-06-28 08:00:00.0 Converts to : 8:0 I am currently using select slotid,dayofshow,forall,alldesc,starttime,endtime, datename(hh,starttime) + ':' + datename(mi,starttime) as starttime2, datename(hh,endtime) + ':' + datename(mi,endtime) as endtime2 from slots I have also tried using DatePart() with the same result. Surely these functions should not do this ? And no I cannot use CF to format it, it has to be in the right format in the query. - Russ Michaels Certified ColdFusion Professional My Blog: www.russ.michaels.me.uk _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< ------------------------------ Message: 4 Date: Mon, 21 Jul 2008 14:39:28 +0100 From: "Peter Boughton" <[EMAIL PROTECTED]> Subject: Re: [CF-Dev] RE: extracting time part from dates To: "Coldfusion Development" <[email protected]> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=UTF-8 > I have also tried using DatePart() with the same result. Surely these > functions should not do this ? They're converting it to numeric - most times that's probably what you want. What about converting to varchar then use Mid() on the resulting string? Or possibly clearer doing a Right( '0' + value , 2 ) ------------------------------ Message: 5 Date: Mon, 21 Jul 2008 16:30:25 +0100 From: Steve Powell <[EMAIL PROTECTED]> Subject: Re: [CF-Dev] RE: extracting time part from dates To: Coldfusion Development <[email protected]> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Left(convert(varchar(20),getdate(),108),5) Sent from my iPhone so please excuse the brevity, poor spelling and any bad grammar. On 21 Jul 2008, at 14:29, "Snake" <[EMAIL PROTECTED]> wrote: > >> - see footer for list info -< > Alas no > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Lynch > Sent: 21 July 2008 14:19 > To: Coldfusion Development > Subject: RE: [CF-Dev] RE: extracting time part from dates > >> - see footer for list info -< > Does swapping 'mi' to 'nn' hold onto the 0? > > Adrian > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Snake > Sent: 21 July 2008 11:35 > To: 'Coldfusion Development' > Subject: [CF-Dev] RE: extracting time part from dates > > >> - see footer for list info -< > OK all I want to do is extract the time from a datetime field in the > database (SQL server), but it is stripping 00 and converting to 0. > > E.G. > > Original column: 2006-06-28 08:00:00.0 > > Converts to : 8:0 > > I am currently using > > select slotid,dayofshow,forall,alldesc,starttime,endtime, > > datename(hh,starttime) + ':' + datename > (mi,starttime) as > starttime2, > > datename(hh,endtime) + ':' + datename(mi,endtime) as > endtime2 > > from slots > > I have also tried using DatePart() with the same result. Surely these > functions should not do this ? > > And no I cannot use CF to format it, it has to be in the right > format in the > query. > > - > > Russ Michaels > > Certified ColdFusion Professional > > My Blog: www.russ.michaels.me.uk > > > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, > go to > http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- >> - cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >> - Lists hosted by www.Gradwell.com -< >> - CFdeveloper is run by Russ Michaels, feel free to volunteer your >> help -< > > > > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, > go to http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- >> - cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >> - Lists hosted by www.Gradwell.com -< >> - CFdeveloper is run by Russ Michaels, feel free to volunteer your >> help -< ------------------------------ _______________________________________________ Dev mailing list [email protected] http://list.cfdeveloper.co.uk/mailman/listinfo/dev End of Dev Digest, Vol 47, Issue 8 ********************************** _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
