?
Solved

Using GETDATE() to return a date only without time

Posted on 2005-03-30
7
Medium Priority
?
34,582 Views
Last Modified: 2011-08-18
I need to use (I think) the GETDATE() function to return the current date, but I need to make sure that it is only the date and no time, or at least time to be 0.
Is this the right way to go about it

CONVERT(DateTime, CONVERT(Char, GETDATE(), 103), 103))

or is there a better way

Thanks in advance
0
Comment
Question by:mikelittlewood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13660333
yours is best one....

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13660342
this is enough right?,  why are u again converting i.e 2 times converting in ur query

select convert(varchar, getdate(), 103)

0
 
LVL 15

Author Comment

by:mikelittlewood
ID: 13660527
Im converting it twice so I get it back in date form of the format 'dd/mm/yyyy 00:00:00' and not something like 'dd/mm/yyyy 12:30:05'
It seems to set the time to 0 which is what I want
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 17

Expert Comment

by:BillAn1
ID: 13660844
you can get slight improvement if instead of converting to a char, you convert to integer this will truncate the time portion. If you want you can cast back to datetime, but this will be done explicitly for you anyway if you assign to a datetime variable, or complare to a datetime field etc.

select cast(cast(getdate() as integer) as datetime)
0
 
LVL 8

Accepted Solution

by:
anthonywjones66 earned 200 total points
ID: 13660897
This is better

CONVERT(datetime, FLOOR(CONVERT(float(24), GETDATE())) )

casting direct to int rounds to the nearest integer which is not what you want

Anthony
0
 
LVL 15

Author Comment

by:mikelittlewood
ID: 13661228
Thanks for the advice all
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 13661509
absolutely right, sorry for the bum steer
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question