Solved

# SQL Convert to DateTime

Posted on 2016-08-22
37 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]
``````
0
Question by:mburk1968
4 Comments

LVL 65

Assisted Solution

Jim Horn earned 125 total points
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

jrb1 earned 125 total points
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 29

Accepted Solution

Olaf Doschke earned 250 total points
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
``````

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
``````

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
``````

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
``````

Bye, Olaf.
1

Author Comment

Awesome! Thank You
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!