SQL Server Date Time Format

SQL Server Date Time Format


USA mm/dd/yy
select convert(varchar, getdate(), 1)

ANSI yy.mm.dd
select convert(varchar, getdate(), 2)

British/French dd/mm/yy
select convert(varchar, getdate(), 3)

German dd.mm.yy
select convert(varchar, getdate(), 4)

Italian dd-mm-yy
select convert(varchar, getdate(), 5)

dd mon yy
select convert(varchar, getdate(), 6)

Mon dd, yy
select convert(varchar, getdate(), 7)

USA mm-dd-yy
select convert(varchar, getdate(), 10)

JAPAN yy/mm/dd
select convert(varchar, getdate(), 11)

ISO yymmdd
select convert(varchar, getdate(), 12)

mon dd yyyy hh:miAM (or PM)
select convert(varchar, getdate(), 100)

mm/dd/yyyy
select convert(varchar, getdate(), 101)

yyyy.mm.dd
select convert(varchar, getdate(), 102)

dd/mm/yyyy
select convert(varchar, getdate(), 103)

dd.mm.yyyy
select convert(varchar, getdate(), 104)

dd-mm-yyyy
select convert(varchar, getdate(), 105)

dd mon yyyy
select convert(varchar, getdate(), 106)

Mon dd, yyyy
select convert(varchar, getdate(), 107)

hh:mm:ss
select convert(varchar, getdate(), 108)

Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
select convert(varchar, getdate(), 109)

mm-dd-yyyy
select convert(varchar, getdate(), 110)

yyyy/mm/dd
select convert(varchar, getdate(), 111)

yyyymmdd
select convert(varchar, getdate(), 112)

Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)

hh:mi:ss:mmm(24h)
select convert(varchar, getdate(), 114)

Important setting when call exe from window services !

Windows service this will not work straight, you need to change some setting for the windows service. A service won't be able to show the GUI of an application, so if the application you are calling has any GUI you won't be able to see it. But the process will get kicked off and you can see the evidence in TaskManager. But we can enable the service to popup the UI by enabling this settings.
Allow Services to interact with desktop
Start > Run > Services.msc
Locate the services which you have created
Right click and select properties.
Select "Log On" tab on the top of the screen
Select "Allow Services to interact with desktop" check box.
Click on "Apply" and start the services.

.NET 2008

Q : How to auto start service after installing?

A :

Private Sub ServiceInstaller_AfterInstall(ByVal sender As System.Object, ByVal e As System.Configuration.Install.InstallEventArgs) Handles ServiceInstaller.AfterInstall

Dim controller = New ServiceController("SERVICE NAME")
controller.Start()

End Sub

Q: How to get Next Weekday date ?
A :
DateAdd("ww", 1, Now)

"yyyy" Year
"q" Quarter
"m" Month
"y" Day of year
"d" Day
"w" Weekday
"ww" Week
"h" Hour
"n" Minute
"s" Second

SQL Server Statement

Q: Reset the auto-increment identity value.

A: "DBCC CHECKIDENT (table_name, RESEED, 0)"

Ref : http://technet.microsoft.com/en-us/library/ms176057.aspx

Crazy on Faye

Diva of Asia - 王菲 [Faye Wong]