getdate format

On Getdate(), how can I return it as 2011-11-30 00:00:00.000 instead of
2011-11-30 10:27:06.130?
VBdotnet2005Asked:
Who is Participating?
 
sventhanCommented:
-- MM/DD/YY date format - Datetime format sql
SELECT TOP (1)
      SalesOrderID,
      OrderDate = CONVERT(varchar(8), OrderDate, 1),
      OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
/*
SalesOrderID      OrderDate         OrderDateTime
75123             07/31/04          2004-07-31 00:00:00.000
*/

more look into this...
http://www.sqlusa.com/bestpractices2005/centurydateformat/
0
 
sammySeltzerCommented:
How about:

CONVERT(char(10), Getdate(), 101)
0
 
sammySeltzerCommented:
Sorry, what I gave you won't work.

Change your data type from dateTime to smalldatetime and that will fix it.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tim_csCommented:
This should work if you want the default format but the time set to midnight.

DATEADD(d,0,DATEDIFF(d,0,getdate()))
0
 
Anthony PerkinsCommented:
You are confusing the output you see in a client tool such as SSMS with reality:  Getdate() returns a datetime data type and datetime data types do not have any format.
0
 
Anthony PerkinsCommented:
As everyone else has suggested you can certainly CONVERT the output to a varchar with the format you like, but that is not a datetime value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.