i need to be able to convert a datetime to the following format ddmmyyyy but cant seem to find the correct convert code.
looked at this but hasnt got the format i want.
http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html
any help is greatly appreciated
Cheers,
Craig
Craig,
You could use something like this:
SELECT replace( convert( varchar(10), getdate(), 104 ), '.', '' )
I hope that you are not storing the data in that format, for it will not be as easy to handle. For storage purposes, use the datetime datatype is best , and even using the ISO form of yyyymmdd and will easily convert back to a datetime datatype. -whereas 'ddmmyyyy' will most likely fail to convert back and require additional steps.
|||no its not for storing it, im actually using it to build another field value 'contactreference' its takes the contacts DOB, Gender & @.@.Identity field. they just want it in a specific format!Cheers,
Craig|||
Crag:
How are you using the @.@.IDENTITY field? It might be helpful if you provide a little more detail to exactly what you are doing.
|||its not the actual identity field sorry. its just the max ID + 1
actually one last wee thing
the following would be the required format:-
04072007M00001
for the 00001 part, how would i be able to pad the identity field so that it always had the correct number of zero's in front of it depending on the @.@.identity value?
say identity was 23, i would need it to be 00023 etc
here's the sql so far
SET @.strContactReference = replace(convert( varchar(10), @.dteDateOfBirth, 104 ), '.', '' ) + CAST(@.strGenderRef as nvarchar(1)) + CAST((@.intMaxContactID +1) as nvarchar(4))
Cheers,
Craig
This might work:
Code Snippet
SET @.strContactReference = replace(convert( varchar(10), @.dteDateOfBirth, 104 ), '.', '' ) +
cast(@.strGenderRef AS nvarchar(1)) +
right(( '00000' + ( cast(( @.intMaxContactID + 1 ) AS varchar(5)))), 5 )
Increase the zero padding and increase the right() quantity (the last 5 above) if your IDENTITY values could possiblly exceed 99,999.
|||104 format reverses the month and the day. Do you want that?
SELECTREPLACE(CONVERT(varchar(10),GETDATE(), 104),'.','') = 07062007
SELECTREPLACE(CONVERT(char(12),GETDATE(), 101),'/','') = 06072007
Adamus
|||I hope the following sample might help you,
Code Snippet
Create Table #customertroubletickets (
[CustomerName] Varchar(100) ,
[AssignedTicketNo] Varchar(100) Unique
);
Insert Into #customertroubletickets Values('Mani','07062007M00001');
Insert Into #customertroubletickets Values('Arni','07062007M00002');
Insert Into #customertroubletickets Values('Kent','07062007M00003');
Insert Into #customertroubletickets Values('Craig','07062007M00004');
Create Proc #AddTroubleTicket
(
@.CustomerName as varchar(100)
)
as
Begin
Declare @.CurrentDate as varchar(8)
Declare @.MaxNo as varchar(20)
Select @.CurrentDate = Replace(Convert(varchar,Getdate(),103),'/','')
Select @.MaxNo = Max([AssignedTicketNo])
From #customertroubletickets
Where [AssignedTicketNo] Like @.CurrentDate + '%'
Select @.MaxNo = Isnull(@.MaxNo,@.CurrentDate +'M00000')
Select @.MaxNo = @.CurrentDate + 'M' +
Substring(Cast(100000 + Cast(Substring(@.MaxNo,10,5) as Int) + 1 as Varchar),2,5)
Insert Into #customertroubletickets with (TABLOCK) Values (@.CustomerName, @.MaxNo)
End
Exec #AddTroubleTicket 'Adams'
Go
Select * From #customertroubletickets
|||It's much easier to use the Format function on the front end form before passing it to the the stored procedure if you have that option.
Or else you can concatentate the 5/0's on the beginnnig of the string and then take the right 5.
DECLARE @.mystring varchar(10)
DECLARE @.myprefix varchar(5)
SET @.mystring ='23'
SET @.myprefix ='00000'
SET @.mystring =RIGHT(@.myprefix +''+ @.mystring, 5)
--SELECT @.mystring
SELECTREPLACE(CONVERT(char(12),GETDATE(), 101),'/','')+'M'+ @.mystring
...or if you want the ddmmyyy:
DECLARE @.mystring varchar(10)
DECLARE @.myprefix varchar(5)
SET @.mystring ='23'
SET @.myprefix ='00000'
SET @.mystring =RIGHT(@.myprefix +''+ @.mystring, 5)
--SELECT @.mystring
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 104), '.', '') + 'M' + @.mystring
Adamus
