Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Convert to DateTime

Posted on 2016-08-22
4
Medium Priority
?
119 Views
Last Modified: 2016-08-22
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

0
Comment
Question by:mburk1968
4 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 41765826
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
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 500 total points
ID: 41765866
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
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 41765877
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
 

Author Comment

by:mburk1968
ID: 41765901
Awesome! Thank You
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question