DanBrother的部落格

Query First and Last Datetime of This Month (T-SQL):

DECLARE @now datetime
DECLARE @first_dt datetime
DECLARE @last_dt datetime
DECLARE @first_dt_concat varchar(30)
DECLARE @last_dt_concat varchar(30)

SET @now = getdate()
SET @first_dt = DATEADD(month,DATEDIFF(month,0,@now),0)
SET @last_dt = DATEADD(s,-1,DATEADD(mm,datediff(m,0,@now)+1,0))

SET @first_dt_concat = convert(varchar(4),datepart(yyyy,@first_dt))+
convert(varchar(2),datepart(mm,@first_dt))+
convert(varchar(2),datepart(dd,@first_dt))+'000000'

SET @last_dt_concat = convert(varchar(4),datepart(yyyy,@last_dt))+
convert(varchar(2),datepart(mm,@last_dt))+
convert(varchar(2),datepart(dd,@last_dt))+'235959'

select 'First DateTime of This Month => ' + CONVERT(varchar(30),@first_dt,20)
union all
select 'Last DateTime of This Month => ' + CONVERT(varchar(30),@last_dt,20)
union all
select 'First DateTime of This Month (Concatenated) => ' + CAST(@first_dt_concat as VARCHAR(30))
union all
select 'Last DateTime of This Month (Concatenated) => ' + CAST(@last_dt_concat as VARCHAR(30))

Here's the output:
------------------------------------------------------------------------------------
First DateTime of This Month => 2014-11-01 00:00:00
Last DateTime of This Month => 2014-11-30 23:59:59
First DateTime of This Month (Concatenated) => 2014111000000
Last DateTime of This Month (Concatenated) => 20141130235959