SQL Convert to DateTime

How do I convert the STDStartDate & STDEndDate to a datetime value to pass into my query  versus text?

SELECT  DATEADD(wk, 0,
                DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkStartDate ,
        DATEADD(wk, 1,
                DATEADD(DAY, 0 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkEndDate ,
        DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS MthStartDate ,
        DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) AS MthEndDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS YearStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS YearEndDate,
        [b]CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-01-01' ELSE '2016-07-01' END AS STDStartDate,
        CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-06-30' ELSE '2016-12-31' END AS STDEndDate;
[/b]

Open in new window

mburk1968Asked:
Who is Participating?
 
Olaf DoschkeSoftware DeveloperCommented:
Your logic in natural language would be

Startdate is 1st of January of the year for current date in the first half of the year.
Startdate is 1st of July of the year for current date in second half of the year.

Enddate is last day of June vs last day of December in first/last half.

Correct?

OK, so lets simple tackle what expressions to use for the dates and the first/second half year periods.

Let's see at some expressions for the dates in question:

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS January1,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, -1) AS June30,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) AS July1,
DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1) AS December31

Open in new window


We don't need the 30th of June, though, the exact half of the year (well, in month exactness) is 1st July midnight, just the moment before June ends. In the same way you rather need the 1st of January next year as endpoint.

And no matter how exactly you judge that, <= '2016-06-30' is wrong, it leaves the full day of 30th June out, you would test for <'2016-07-01' to take everything up to the moment before midnight July, 1st to the first half.

You can put GetDate() into the first or second half of a year much simpler though, by testing quarter being1,2 vs 3,4.

 So let's see this:

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS January1,
DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) AS July1,
DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) AS January1NextYear,
DATEPART(qq,GETDATE()) as CurrentQuarter

Open in new window


So finally, the start and enddate as you need them:
CASE WHEN DATEPART(qq,GETDATE()) <3 THEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ELSE DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) END AS STDStartDate

Open in new window


and
CASE WHEN DATEPART(qq,GETDATE()) <3 THEN DATEADD(qq, DATEDIFF(yy,0,getdate())*4+2, 0) ELSE DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) END AS STDEndDate

Open in new window


Bye, Olaf.
1
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CAST('2016-06-30' as datetime)

This assumes that the value is convertable to date time, and you can test that like this..

CASE
   WHEN ISDATE('2016-06-30') = 1 THEN -- good
   ELSE -- not a date
END
1
 
jrb1Commented:
And you can apply the CAST one time for each field:

SELECT  DATEADD(wk, 0,
                DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkStartDate ,
        DATEADD(wk, 1,
                DATEADD(DAY, 0 - DATEPART(WEEKDAY, GETDATE()),
                        DATEDIFF(dd, 0, GETDATE()))) AS WkEndDate ,
        DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS MthStartDate ,
        DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) AS MthEndDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS YearStartDate ,
        DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS YearEndDate,
        CAST(CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-01-01' ELSE '2016-07-01' END as datetime) AS STDStartDate,
        CAST(CASE WHEN GETDATE() <= '2016-06-30' THEN '2016-06-30' ELSE '2016-12-31' END as datetime) AS STDEndDate;
0
 
mburk1968Author Commented:
Awesome! Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.