Developers Network

Set Width of GridView BoundField

2013-08-05T21:15:00.000-07:00

ItemStyle-Width="40%" ItemStyle-Wrap="false" in the BoundField tag

Create A Comma Delimited List From a Column

2012-10-08T06:35:00.001-07:00

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

And here is the expected output -

Id	AllNames
1	A, B, C
2	A, B
3	X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table
CREATE TABLE Test
(
Id INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,'Z'
GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id
,AllNames = SubString (( SELECT ', ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id
,AllNames = REPLACE (( SELECT T2.Names AS 'data()'
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), ' ', ', ')
FROM Test as T1
GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id
,AllNames = SubString (( SELECT '| ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

Work out min and hours

2012-07-19T23:16:00.002-07:00

I have a minute value and i want to have to 2 string values one with how many hours and the other with the minutes, e.g.:

Value - 121 minutes

var span = System.TimeSpan.FromMinutes(121);
var hours = ((int)span.TotalHours).ToString();
var minutes = span.Minutes.ToString();

The ToString() is because you asked for string values ...

TotalHours are the complete hours in the TimeSpan, they can be more than 24 (whereas the "Hours" field has a maximum of 24)

Oh, and on second thought: Why use the TimeSpan and not calculate it yourself? Because TimeSpan is already there debugged & tested by Microsoft, it has a nice clean interface (looking at the code you easily see whats going on without having to follow a calculation mentally) and it easily extends to further solutions. (Have the input in seconds? Use TimeSpan.FromSeconds(). Want the days? Use span.TotalDays ...)

Update:

I just noticed mistake in my answer: TotalHours returns a fractional value of all the hours, so we have to truncate it to an integer before converting it to a string.

Extended Date Formats

2012-06-25T23:54:00.001-07:00

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY ¹	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006 ¹
Mon YYYY ¹	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 ¹
Month YYYY ¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 ¹
DD Month ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September ¹
Month DD ¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 ¹
DD Month YY ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 ¹
DD Month YYYY ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 ¹
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY ¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 ¹
Mon-YYYY ¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 ¹
DD-Mon-YY ¹	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 ¹
DD-Mon-YYYY ¹	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005 ¹

Standard Date Formats

2012-06-25T23:53:00.000-07:00

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY ¹ HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM ¹
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
DD Mon YY ¹	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 ¹
DD Mon YYYY ¹	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 ¹
Mon DD, YY ¹	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 ¹
Mon DD, YYYY ¹	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 ¹
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) ¹	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM ¹
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
DD Mon YYYY HH:MM:SS:MMM(24h) ¹	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 ¹
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical (with milliseconds)	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM ¹	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM ¹
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Proper Title Case

2010-04-15T22:31:00.000-07:00

CREATE FUNCTION fn_ProperCase(@x varchar(7999))
RETURNS varchar(7999)
AS
BEGIN

DECLARE @y int
SET @y = 1

SELECT @x = UPPER(SUBSTRING(@x,1,1))+LOWER(SUBSTRING(@x,2,LEN(@x)-1))+' '

WHILE @y < LEN(@x)
BEGIN
SELECT @y=CHARINDEX(' ',@x,@y)
SELECT @x=SUBSTRING(@x,1,@y)+UPPER(SUBSTRING(@x,@y+1,1))+SUBSTRING(@x,@y+2,LEN(@x)-@y+1)
SELECT @y=@y+1
END
RETURN @x
END

SELECT dbo.fn_ProperCase('THE QUICK FOX JUMPED OVER THE LAZY DOG')

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

2009-12-02T22:25:00.000-08:00

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Functin - How to Get Leap Year

2009-11-01T20:46:00.000-08:00

/*Detecting leap year*/
CREATE FUNCTION dbo.IsLeapYear(@Year int)
RETURNS bit
AS
BEGIN
DECLARE @RetVal bit
IF (@Year % 400) = 0 --//every 400 years
SET @RetVal = 1
ELSE
BEGIN
IF (@Year % 100) = 0
SET @RetVal = 0
ELSE
BEGIN
IF (@Year % 4) = 0
SET @RetVal = 1
ELSE
SET @RetVal = 0
END
END
RETURN @RetVal
END
GO

MaxLength not working in ASP.NET textbox multiLine

2009-10-12T22:39:00.000-07:00

Great code I found for maxlength issue with .NET textboxes. .NET textboxes, if TextMode is set to MutilLine, the maxlength property just doesn't work, sux. So after playing around with different soloutions. I found this on, thanks to a guy named "Leo." So a big thanks to him!

Add this function in JavaScript on your page, I stuck it my "functions.js" file I include on the page anyway.

function checkMaxLen(txt,maxLen) {
try{
if(txt.value.length > (maxLen-1)) {
var cont = txt.value;
txt.value = cont.substring(0,(maxLen -1));
return false;
};
}catch(e){
}
}

Then on the textbox use something like this:


<asp:TextBox runat="server" ID="txtComments" CssClass="comment_textbox" Height="75px" TextMode="MultiLine" onkeyup="return checkMaxLen(this,151)"></asp:TextBox>

Notice the function passes the textbox (this) and the maxlength you want, in this case 150, notice I have 151, the function will limit the maxlength minus one... So if you want to limit the textbox with mutiline set to 100, you enter 101, make sense?

Linked Server - How to pass a variable to a linked server query

2009-07-22T05:46:00.000-07:00

This article describes how to pass a variable to a linked server query.

When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query.

To pass a variable to one of the pass-through functions, you must build a dynamic query.

Any data that includes quotes needs particular handling. For more information, see the "Using char and varchar Data" topic in SQL Server Books Online and see the following article in the Microsoft Knowledge Base:

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

      DECLARE @TSQL varchar(8000), @VAR char(2)
     SELECT  @VAR = 'CA'
     SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
     EXEC (@TSQL)

Pass the Whole Query

When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), use code that is similar to the following sample:

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL)

Use the Sp_executesql Stored Procedure

To avoid the multi-layered quotes, use code that is similar to the following sample:

DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
    N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
    N'@state char(2)',
    @VAR

Upgrading to IE8 breaks debugging with Visual Studio 2005

2009-06-14T23:40:00.000-07:00

Since Microsoft published IE8 as a Windows Update and my boss said he upgraded to it, I figured it was safe to upgrade to it as well. I’ve been enjoying the changes for a couple days now, but when I attempted to debug an ASP.NET application with Visual Studio 2005, it would no longer stop at a breakpoint. Pretty frustrating if you ask me. I’ve found a few articles online describing problems, most of which I haven’t encountered yet. You may want to implement all these workarounds.

The problem I was having appears to be related to how IE8 uses multiple processes. Apparently VS 2005 doesn’t know which process to attach to. Here are three solutions:

1.Don’t have IE8 running at the time you want to debug. This worked for me.
2.Modify the registry as described in the second link above. This worked for me as well. Here are the steps:
1.Open RegEdit
2.Browse to HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Internet Explorer -> Main
3.Add a DWORD value called TabProcGrowth with a value of 0
4.Turn off protected mode browsing in IE (Security page of Internet Options) if you are running on Vista or newer.
3.Uninstall IE8 to revert back to IE7. I don’t really want to do this though. I’ve grown particularly fond of a few of the new features in IE8, such as previous session restore (although I wish it would allow me to restore more than just the last session) and improvements in search.

I don’t know if this is a problem with VS 2008, but some of the articles seem to imply it only applies to VS 2005. Maybe it’s time to upgrade Visual Studio

Referencing 2.0 Web Services (asmx) in Visual Studio 2008

2009-03-24T04:06:00.000-07:00

Recently I needed to add a reference to a classic (.Net 2.0) web service inside a new project that was being created via Visual Studio 2008.
Now, I have not done a lot of work with WebServices in the past 6-9 months, but thought it would be cake. Please keep in mind, the service I wanted to connect to was NOT a WCF Service, it was a standard ASMX service.
Below is what I thought would work, but did not

1- Attempt to Add A Service Reference

2- Choose Services in Solution (my Project is in the same solution file).

3- View the generated service implementation.

4- WCF Style code implementation

What you will notice from step 4 is that the way you go about implementing this service is in the WCF style. This is not what I was looking for. I was wanting to reference this like all the other code in our projects.

Below is what DID work

2- Attempt to Add A Service Reference

3-Need to add the service as a Web Reference

4- Finally need to search for and find your web service
5- Lastly, if you have done everything correctly, you should see the following.
Now I know that the preferred service type is now WCF, but come-on not everyone is using WCF just yet. Adding a traditional web service reference is way too much friction. Why is not possible to add a reference from the solution explorer? Really can anyone answer me that.
Till next time,

Date/Time Conversions Using SQL Server

2009-03-19T06:10:00.000-07:00

Problem
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output
select replace(convert(varchar, getdate(),101),'/','') 12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') 12302006004426