|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DateTime formatis their any function in sql server to format date like
ddmmyyyyhhmm i want to create a key from date but date is bydefault like 12/12/2004 12:30:30 but i want to convert it to like we do in vb format("ddmmyyyyhhmm",date)... thanks amjad wrote:
> is their any function in sql server to format date like The CONVERT function can convert many things... datetimes among them.> ddmmyyyyhhmm i want to create a key from date but date is bydefault like > 12/12/2004 12:30:30 but i want to convert it to like we do in vb > format("ddmmyyyyhhmm",date)... thanks You could it like this: "DECLARE @myDate datetime SET @myDate=GETDATE() SELECT CONVERT(datetime, @myDate, 120)" This link has more information about CONVERT: http://msdn2.microsoft.com/en-us/library/ms187928.aspx "amjad" <am***@discussions.microsoft.com> wrote in message Hinews:47ED88D8-59AB-4C34-A831-1DD8C3C6601E@microsoft.com... > is their any function in sql server to format date like > ddmmyyyyhhmm i want to create a key from date but date is bydefault like > 12/12/2004 12:30:30 but i want to convert it to like we do in vb > format("ddmmyyyyhhmm",date)... thanks Couple of points from my limited experience. 1. Using a datetime as a key field is asking for trouble. 2. Typically, formatting a datetime is done on the client rather than the database engine - however it is possible to use the CONVERT function to change the format of a datetime if its required. I hope this helps Thanks Chris. i am not using just datetime field for key field but combing with another
fields... weather its a bad move or good move as long its work for me i dont care.... please if you dont know then dont say bad movessss. you dont my work. thanks Show quote "Chris Strug" wrote: > "amjad" <am***@discussions.microsoft.com> wrote in message > news:47ED88D8-59AB-4C34-A831-1DD8C3C6601E@microsoft.com... > > is their any function in sql server to format date like > > ddmmyyyyhhmm i want to create a key from date but date is bydefault like > > 12/12/2004 12:30:30 but i want to convert it to like we do in vb > > format("ddmmyyyyhhmm",date)... thanks > > Hi > > Couple of points from my limited experience. > > 1. Using a datetime as a key field is asking for trouble. > 2. Typically, formatting a datetime is done on the client rather than the > database engine - however it is possible to use the CONVERT function to > change the format of a datetime if its required. > > I hope this helps > > Thanks > > Chris. > > > Are you familiar with the idea of giving someone enough rope with
which to hang themselves? I hope this isn't rope. select LEFT(REPLACE(convert(varchar(30),getdate(),104),'.','') + REPLACE(convert(varchar(5),getdate(),114),':',''), 12) There are plenty of times when a datetime column is a key, or part of a key. I certainly would not want a character string such as the above used as a key. Formatting of dates, as has been said by others, is better handled by the front-end program. Roy Harvey Beacon Falls, CT On Fri, 18 Aug 2006 07:13:28 -0700, amjad <am***@discussions.microsoft.com> wrote: Show quote >is their any function in sql server to format date like >ddmmyyyyhhmm i want to create a key from date but date is bydefault like >12/12/2004 12:30:30 but i want to convert it to like we do in vb >format("ddmmyyyyhhmm",date)... thanks thanks i have done exactly you show to me... but i was looking in one
function... i dont have front end as i can do it with c# and vb.net... but i am writing a store proc....... thanks any way Show quote "Roy Harvey" wrote: > Are you familiar with the idea of giving someone enough rope with > which to hang themselves? > > I hope this isn't rope. > > select LEFT(REPLACE(convert(varchar(30),getdate(),104),'.','') + > REPLACE(convert(varchar(5),getdate(),114),':',''), > 12) > > There are plenty of times when a datetime column is a key, or part of > a key. I certainly would not want a character string such as the > above used as a key. Formatting of dates, as has been said by others, > is better handled by the front-end program. > > Roy Harvey > Beacon Falls, CT > > On Fri, 18 Aug 2006 07:13:28 -0700, amjad > <am***@discussions.microsoft.com> wrote: > > >is their any function in sql server to format date like > >ddmmyyyyhhmm i want to create a key from date but date is bydefault like > >12/12/2004 12:30:30 but i want to convert it to like we do in vb > >format("ddmmyyyyhhmm",date)... thanks > > thanks i have done exactly you show to me... but i was looking in one So why can't you create a function????> function... i dont have front end as i can do it with c# and vb.net... but > i > am writing a store proc....... > thanks any way USE tempdb GO CREATE FUNCTION dbo.HangYourself ( @dt SMALLDATETIME ) RETURNS CHAR(12) AS BEGIN RETURN ( SELECT LEFT ( REPLACE(convert(varchar(30),@dt,104),'.','') + REPLACE(convert(varchar(5),@dt,114),':',''), 12 ) ) END GO SELECT dbo.HangYourself('20060812 3:45'); GO DROP FUNCTION dbo.HangYourself; GO Aaron Bertrand [SQL Server MVP] wrote:
>> thanks i have done exactly you show to me... but i was looking in one Because Roy didn't do it for him... :-)>> function... i dont have front end as i can do it with c# and vb.net... but >> i >> am writing a store proc....... >> thanks any way > > So why can't you create a function???? > >> So why can't you create a function???? Sorry, my bad! Next we're going to get server credentials so we can run the >> > > Because Roy didn't do it for him... :-) CREATE FUNCTION script, too. :-)  |
|||||||||||||||||||||||
Other interesting topics