23rd May, 2007

SQL Server function for Date manipulation - convert()

I used convert() function a lot since SQL Server does not have “Date” data type so all date you stored in system are always have a time attached to it. However, a lot of time when I created the report, I want to group the data by date, then the convert() function become a very handy function to help me to “trunc” (if you are oracle user then you know what I’m talking about) the datetime column.

Example #1: differences between before/after using the convert

select convert(varchar, getdate(),101) as date_only, getdate() as full_date_time

date_only full_date_time
----------- -------------------------
05/23/2007 2007-05-23 18:03:20.450

Example #2: Group result by day — e.g.: how many users registered each day

select convert(varchar(10), registered_date, 101) as registration_date, count(*) as user_count
from user_registration
group by convert(varchar(10), registered_date, 101)

registration_date user_count
---------------- -----------
05/15/2007 2
05/17/2007 5
05/18/2007 1
05/19/2007 10
05/20/2007 8

Example #3: includes all data in a specify date

select count(*) as user_count
from user_registration
where convert(varchar(10), registered_date, 101) = '05/15/2007'


Note that if you write your query as
select count(*) as user_count
from user_registration
where registered_date= '05/15/2007'


You are very highly possible to get zero row count, because the user have to be registered at exactly ‘2007-05-15 00:00:00′ to be able to qualify for your query criteria! See the difference? ;)

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • De.lirio.us


Thanks so much!

That is good guide!

Leave a response

Your response: