SQL time-ish stuff

Posted on by

Extract the time:

--get the time as an hour
DATEPART(hh,getdate())
Extract the dayname:
--get the day as a word (short)
UPPER(left(datename(dw,getdate()),3))
--get something nicely formatted.
substring(convert(char,getdate() ,120),12,5)

Create a table full of sequential date_time

USE tempdb
DECLARE @t int
DECLARE @inc_date datetime 
SET @inc_date = '2001-01-01 23:59:59'  
--CHANGE THIS TIME TO GET DIFFERENT SNAPSHOT TIME first date and time of temp table 

--if the temp table exists drop and recreate with the date time supplied above	
IF  (SELECT '1' as exist_ WHERE exists (select '1' from sysobjects  WHERE  name='##Temp_dates')) = '1' 
	BEGIN DROP TABLE  ##Temp_dates END
--create the table
CREATE TABLE ##Temp_dates(t_date datetime NOT NULL PRIMARY KEY) 
	SET NOCOUNT ON  
WHILE @inc_date <= '2012-12-31' 
	BEGIN 
	INSERT ##Temp_dates (t_date) VALUES (@inc_date) 
	SET @inc_date = @inc_date + 1 --or decimal value for hours 0.5 etc..
	END

Leave a Reply