Formatting Datetime – making time blank | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Formatting Datetime – making time blank

I need to automatically insert today’s date into a datetime field but ensure the time part is blank so the result is like ‘2011-02-23 00:00:00:000’. Does anyone know how to use the GETDATE() function to format this so the time is blank?
Also does anyone know why the application won’t edit the record because the time part is entered?
Thanks!!!!

Try DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Preferrably you would use the DATE data type from SQL Server 2009 onwards.

Hi Frank,have you updated your article about handling dateTime? It would be nice to have a link ready as an instant answer for this type of questions.
I guess, it’s about time to consider this seriously. It’s overdue… [:$]
datetime is just a float, thus use;
1. cast(cast(getdate() as int) as datetime) <= fast but may not work for all versions
or
2. cast(floor(cast(getdate() as float)) as datetime) <= deterministic + extra ‘floor’ cost

I believe that even with the new types available from SQL Server 2008 onwards the internal storage is still based on combining integers into a binary(…) column. As far as I’m aware it was never a float.

[quote user="FrankKalis"]
I believe that even with the new types available from SQL Server 2008 onwards the internal storage is still based on combining integers into a binary(…) column. As far as I’m aware it was never a float.
[/quote]Not sure about that. SQL Server version after 2000 borrowed a lot from ORACLE. In Oracle DATE contains time as well and it is float. BOL are not explicit about that, so I don’t know, it needs to be investigated.
[quote user="FrankKalis"]
Try DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Preferrably you would use the DATE data type from SQL Server 2009 onwards.
[/quote]
[:)]
A typo .It should be 2008 [;)]
hi frank – this did the job! many thanks u r very helpful :) did u code this yourself or did you have this from somewhere?

Thanks!
This is a quite well-know technique for setting the time to midnight. I didn’t come up with that myself, but rather picked it up from the former MS newsgroups. It is a little bit more difficult to understand why and how it works. But once this is clear, it is really an easy and elegant way. Now, I only wish we were already on SQL Server 2008 with its DATE data type, so that this workaround is not longer needed, but that’s a different story. [:)]

I would say Frank’s contribution on DATETIME related information are quite helpful and handy to keep for every user. I always refer to his article for any date/time related development that I may get involved [:)].

[quote user="Trev256"]
I need to automatically insert today’s date into a datetime field but ensure the time part is blank so the result is like ‘2011-02-23 00:00:00:000’. Does anyone know how to use the GETDATE() function to format this so the time is blank?
Also does anyone know why the application won’t edit the record because the time part is entered?
Thanks!!!!
[/quote]
Where do you want to show formatted dates? If you use front end application, do formation there
hi frank – I worked out how the code works! Is there any place on the web where i can upload my own articles?
Well, you can always go to Blogspot or WordPress and create your own blog and start publishing. It’s almost instantly and quite straightforward. really. :)
hi frank – which is the best one to use – blogspot or wordpress? i will take your advice… :)
Given these two options, I would use Blogger (Blogspot) if you intend to stay on a hosted platform, or if you wfirst want to get familiar to blogging. If you later on intend to go and host somewhere your own domain, I’d go with WordPress.
Wordpress is better in this case, as you can manage the posts and directly input the blog post using an email (Windows essentials).
Blogger caught up on this one recently, I believe. But, yes, really both offer all a beginner needs. No big difference at all., I mean.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |