About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, October 01, 2007

Reminder: TSQL date only

Get the date portion of datetime, correctly:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))


since,

CONVERT(VARCHAR(20),GETDATE(),112) returns the wrong date sometimes.

-----A cool and faster way:


DECLARE @datemask bigint
DECLARE @timemask bigint

SET @datemask = 0xffffffffff000000
SET @timemask = 0x0000000000ffffff

SELECT CAST(CAST((CAST(getdate() as binary) & @datemask) as binary(8)) as datetime)



//IF you need to grab a date and a time from two different locations in the database and combine them into one datetime (ex: shifts(time) and payperiod(date)):

SELECT CAST(CAST((CAST(getdate() as binary) & (@datemask | (CAST((CAST(getdate() as binary) & (@timemask) ) as binary(8)))) ) as binary(8)) as datetime)

No comments: