Some simple examples of SQL time functions (local time on the local machine is not available on foreign servers)

 

Code

 

Time conversion:

Select convert (varchar (12), getdate (), 101) 07/16/2010
Select convert (varchar (12), getdate (), 102) 2010.07.16
Select convert (varchar (12), getdate (), 103) 16/07/2010
Select convert (varchar (12), getdate (), 104) 16.07.2010
Select convert (varchar (12), getdate (), 105) 16-07-2010
Select convert (varchar (12), getdate (), 106) 16 Jul 2010
Select convert (varchar (12), getdate (), 107) Jul 16,201 0
Select convert (varchar (12), getdate (), 108) 11:58:49
Select convert (varchar (12), getdate (), 109) Jul 16 2010.
Select convert (varchar (12), getdate (), 110) 07-16-2010
Select convert (varchar (12), getdate (), 111) 2010/07/16
Select convert (varchar (12), getdate (), 112) 20100716
Select convert (varchar (12), getdate (), 113) 16 Jul 2010
Select convert (varchar (12), getdate (), 114) 11: 59: 35: 930



-- The first day of the month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)

-- Monday of this week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)

-- The first day of this year
Select dateadd (yyyy, datediff (yyyy, 0, getdate (), 0)

-- On the last day of this month, note that SQL is accurate to 3 ms...


Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate () + ))


-- On the last day of the week, note that SQL is accurate to 3 ms...

Select dateadd (MS,-3, dateadd (wk, datediff (wk, 0, getdate () + ));


-- The first Monday of this month

Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0 );

 

 

Note: This example cannot be used on foreign servers. I will study the timing and time zone problems of American servers.