Can I make SQL Server format dates and times for me?

Many people have asked if there is a way to make SQL Server behave the way FORMAT works in VB (and FormatDateTime in VBScript). What they'd like to see is the ability to tell SQL Server to format a date with long date and time, or in MM/DD/YYYY format, instead of having to memorize existing format conversion numbers and/or manipulate the strings themselves. For example, to get today's date in YYYYMMDD format, you currently need to call the following:

SELECT CONVERT(CHAR(8), GETDATE(), 112)

What does the 112 mean? Nothing. It's just an arbitrary number representing this specific format (Kalen Delaney's Inside SQL Server 2000 has a detailed explanation of the more commonly-used conversions).

Now, wouldn't it be nice to be able to say this:

SELECT CONVERT(VARCHAR, GETDATE(), 'YYYYMMDD')

?

Well, now you can, if you're using SQL Server 2000. I designed this scalar user-defined function for specifically this purpose.

CREATE FUNCTION dbo.FormatDateTime
(
@dt DATETIME,
@format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format

WHEN 'LONGDATE' THEN

DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

WHEN 'LONGDATEANDTIME' THEN

DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20),
@dt - CONVERT(DATETIME, CONVERT(CHAR(8),
@dt, 112)), 22), 11)

WHEN 'SHORTDATE' THEN

LEFT(CONVERT(CHAR(19), @dt, 0), 11)

WHEN 'SHORTDATEANDTIME' THEN

REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
'AM', ' AM'), 'PM', ' PM')

WHEN 'UNIXTIMESTAMP' THEN

CAST(DATEDIFF(SECOND, '19700101', @dt)
AS VARCHAR(64))

WHEN 'YYYYMMDD' THEN

CONVERT(CHAR(8), @dt, 112)

WHEN 'YYYY-MM-DD' THEN

CONVERT(CHAR(10), @dt, 23)

WHEN 'YYMMDD' THEN

CONVERT(VARCHAR(8), @dt, 12)

WHEN 'YY-MM-DD' THEN

STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
5, 0, '-'), 3, 0, '-')

WHEN 'MMDDYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

WHEN 'MM-DD-YY' THEN

CONVERT(CHAR(8), @dt, 10)

WHEN 'MM/DD/YY' THEN

CONVERT(CHAR(8), @dt, 1)

WHEN 'MM/DD/YYYY' THEN

CONVERT(CHAR(10), @dt, 101)

WHEN 'DDMMYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

WHEN 'DD-MM-YY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

WHEN 'DD/MM/YY' THEN

CONVERT(CHAR(8), @dt, 3)

WHEN 'DD/MM/YYYY' THEN

CONVERT(CHAR(10), @dt, 103)

WHEN 'HH:MM:SS 24' THEN

CONVERT(CHAR(8), @dt, 8)

WHEN 'HH:MM 24' THEN

LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

WHEN 'HH:MM:SS 12' THEN

LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

WHEN 'HH:MM 12' THEN

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

ELSE

'Invalid format specified'

END
RETURN @dtVC
END
GO

(If you're using SQL Server 7.0, you can't create UDFs; so, I suppose you could put this logic into a stored procedure, and put the result into an output parameter.)

Sample usage:

DECLARE @now DATETIME
SET @now = GETDATE()

PRINT dbo.FormatDateTime(@now, 'LONGDATE')
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'SHORTDATE')
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'YYMMDD')
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')
PRINT dbo.FormatDateTime(@now, 'MMDDYY')
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')
PRINT dbo.FormatDateTime(@now, 'DDMMYY')
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM 24')
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')
PRINT dbo.FormatDateTime(@now, 'HH:MM 12')
PRINT dbo.FormatDateTime(@now, 'goofy')

Create EXE or Setup File in .NET Windows Application

Hi .NET Architects!

Visual Studio 2005 Setup and deployment Projects provides to make an exe or installer file in .NET windows application is easy and rapidly.

Visual Studio provides templates for four types of deployment projects: Merge Module Project, Setup Project, Web Setup Project, and Cab Project. In addition, a Setup wizard is provided to help step you through the process of creating deployment projects. You can see the templates and the wizard in the New Project dialog box under the Setup and Deployment Projects node.

The following are guidelines for choosing the right type of deployment project for your project.


1)Merge Module Project : Packages components that might be shared by multiple applications.

2)Setup Project : Builds an installer for a Windows-based application.
3)Web Setup Project : Builds an installer for a Web application.
4)Cab Project : Creates a cabinet file for downloading to a legacy Web browser.
5)Smart Device Cab Project : Creates a Cab project to deploy device applications.

This topic demonstrates deployment using a Setup and Deployment Project.

Setup Projects

Setup projects allow you to create installers in order to distribute an application. The resulting Windows Installer (.msi) file contains the application, any dependent files, information about the application such as registry entries, and instructions for installation. When the .msi file is distributed and run on another computer, you can be assured that everything necessary for installation is included; if for any reason the installation fails (for example, the target computer does not have the required operating system version), the installation will be rolled back and the computer returned to its pre-installation state.

The following steps will gives the elegant guidance to create an exe or installer file.

1, Go to file menu > click Add > new project >now “Add New Project” dialog will appear.




2.Select the “Other Project Types” and click “Setup and Deployment” projects,Choose “Setup Project”give name project name in name text box finally click OK.




3.New project appear in solution explorer,for eg., you have give the name “MyEXE” file will be displays with given name.




4.Right click the MyEXE > go View > click “File System”




5.You can see the “File System on TargetMachine”under three folders
Application Folder
User’s Desktop
User’s Program Menu




6.Select Application Folder and right click Add>Project Output>select Primary output



7. Select User’s Desktop richt click on the second window>click create new shortcut>select output file from Application folder>change the file name from primary output name to MyEXE



next >>




same procedure follows the user’s program menu also


8.If you want to change the Manufactures name for exe,just right click the project go to properties



change the properties as per you requirement

9.Finally Build the new project After successfully Build the project myEXE(Setup) will be appear in Application Debug or Release folder(depend upon the properties settings)



EXE or installer will be available on his physical path…




When you want to install the EXE on the client machine,you should be installed .NET Framework on that machine because, Applications and controls written for the .NET Framework v2.0 requires the .NET Framework Redistributable Package version 2.0 to be installed on the computer where the application or control runs.

Apache Server On Xampp - Problems in starting Apache service on xampp

Display Parent Child Hierarchy through SQL Query

SELECT Id, Title, (SELECT COUNT(*) AS Expr1 FROM Category WHERE (CategoryParent_Id = C.Id)) AS childnodecount FROM Category AS C WHERE (Id = (SELECT Id FROM Category AS Category_1 WHERE (Id = @parentId) AND (CategoryParent_Id IS NULL))) OR (CategoryParent_Id = @parentID) AND (IsActive = 1) AND (IsDeleted = 0)

Error in SQL Server 2005 :: Cannot connect to ComputerName in Windows Vista

Hi,
Since 3 months i was suffering with this error at the last i find solution :
1. My SQL Express was working finely but when i was supposed to created Table it was not allowing me to create that.
2. My Main account of SQL Server 2005 was also not working, it was showing the same error ''Cannot connect to ComputerName'' with error number 18456.

Today finally check lots of posts but i find one post where i find solution.
Solution is very simple run SQL Server 2005 with Administrator account.
ie., Click on Start button then goto SQL Server 2005 and goto SQL Server Management Studio and Right Click there and goto Run As Administor.
That is it.

Thank You!.

How to convert Green Free Space colour partion into Unallocated Space in Computer Management / Disk Management Window: Vista

Download the below software its a demo software to make the partions securely.
http://www.extend-partition.com/download/ePart_Free.exe
As Its not free software it will ask u to Proceed for the payment. Click on Proceed button it will poup a message "Buy Now" and "Buy Later" click on Buy Later and that is all.

Check again "Disk Management" Green Free Space colour partion has been converted into Unallocated Space.

Comments Please