date format, select convert(varchar(8), getdate(), 1)

With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but

How can I get time such as mmddyyyyhhmm

Thanks
Daniel

I do not believe there is a standard format that will give you exactly what you are wanting. Here is how to do it using DATEPART and REPLICATE to pad the items out.

Code Snippet

SELECT REPLICATE('0', 2-LEN(DATEPART(mm, getdate()))) + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(dd, getdate()))) + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + CAST(DATEPART(YY, getdate()) AS VARCHAR(4)) + REPLICATE('0', 2-LEN(DATEPART(hh, getdate()))) + CAST(DATEPART(hh, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(mi, getdate()))) + CAST(DATEPART(mi, getdate()) AS VARCHAR(2))

|||

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

This will get the format of hh:mms (108) and you just replace the : with nothing and take the last 2 characters off.

|||

Very good

I have mmddyyyyhhmm but a little error

I get 04/19/20071223

should be 04/19/2007 12:23

Thanks
Daniel

|||

Code Snippet

create function dbo.fn_FormatADate(

@.dtInput datetime

)

returns char(13)

as

begin

return replace(Replace( convert(varchar,@.dtInput,101) + left(convert(varchar,@.dtInput,108),5) ,'/',''),':','')

end

GO

select dbo.fn_FormatADate(getdate())

GO

|||

select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)

That should give you the format that you illustrated in your "should be".

|||

Hi Ben,

Glad to see you here.

Regards,

Alejandro Mesa

|||

Just a thought...since we always know the len of the output, replicate is a bit more combersome than using "RIGHT"...

Code Block

declare @.TheDate datetime

set @.TheDate = GetDate()

select

right('0' + convert(varchar,datepart(Month,@.TheDate)), 2) --The Month

+ right('0' + convert(varchar,datepart(Day,@.TheDate)), 2) --The Day

+ convert(varchar,datepart(Year,@.TheDate)) --For the Year

+ right('0' + convert(varchar,datepart(hour,@.TheDate)), 2) --The hour

+ right('0' + convert(varchar,datepart(minute,@.TheDate)), 2) --The minute

Date format in a merged field

In the footer from a report I want to print the UserID and the Date. I added a textbox with de following code: =User!UserID & " " & Globals!ExecutionTime

Now I want to change the date format in dd-MM-yyy uu:mm. This is not possible in the textbox properties because I added the UserID to the same textbox. Is there a way to change the format?

Try this:

= User!UserID & " " & Format(Globals!ExecutionTime,"dd-MM-yyyy hh:mm")

cheers,

Andrew

|||

You need to use the .NET String.Format method e.g.

=User!UserID & " " & String.Format(Globals!ExecutionTime, "dd-MM-yyy uu:mm")

|||Thanks a lot! It works!