Help formatting date with preset hour and minute

Question

Could someone please help:

     I have a need to format a start date and time and an end date and time in the following format.  I need to format my start date as  '2017-01-01 00:00'  and my end date as '2017-01-31 23:59'.  I have tried the following and it does print correctly using a select,  but it does not work in a query.

My example of the current date.

DECLARE @cur_date VARCHAR(25);
DECLARE @time_start VARCHAR(6);
DECLARE @time_end VARCHAR(6);

SET @time_start = ' 00:00';
SET @time_end = ' 23:59';
SET @cur_date = CONVERT(VARCHAR, GETDATE(), 101);

SELECT @cur_date + @time_start AS 'cur_date_start' -- Yields '03/07/2017 00:00'

When I try using the date variables in my query  as show below, I get this error
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

SELECT * FROM sales s WHERE s.orderdate BETWEEN +  @cur_date + @time_start
                                         AND     '2017-01-31 23:59'

Coulc someone please give some ideas on how to achieve a working way to do this.


thanks in advance.

---

Duckkiller53

Answer 1

DECLARE @cur_date VARCHAR(25);
DECLARE @time_start VARCHAR(6);
DECLARE @time_end VARCHAR(6);

SET @time_start = ' 00:00';
SET @time_end = ' 23:59';
SET @cur_date = CONVERT(VARCHAR, GETDATE(), 101);

SELECT try_cast(@cur_date + @time_start  as datetime) AS 'cur_date_start' --  '2017-03-07 00:00:00.000'

SELECT * FROM sales s WHERE s.orderdate BETWEEN try_cast(@cur_date + @time_start  as datetime)
                                         AND   try_Cast('2017-01-31 23:59' as datetime)

Answer 2

Try to remove + sign after BETWEEN.

---

A Fan of SSIS, SSRS and SSAS

Answer 3

Take a step back and think about what you are trying to do logically. Define your goal using language and not code. You want to select rows there orderdate is sometime between the beginning of the current date and the end of the current date. Now express that in terms of code.

where s.orderdate >= cast(GETDATE() as date) and s.orderdate  < dateadd(day, 1, cast(GETDATE() as date) )

And notice carefully the logic. Now go review the documentation about the accuracy of the datatype of orderdate. You made an assumption - which might be safe now. But why entangle that assumption into your logic without any real need? For more information, see Tibor's discusson of datetime here.  And if you REALLY need to use the upper boundary you wrote, you can use the following as a guide:

select GETDATE() as x, 
format(getdate(), 'yyyyMMdd') + N' 23:59:00.000' as y, 
cast(format(getdate(), 'yyyyMMdd') + N' 23:59:00.000' as datetime) as z 
;

Answer 4

Deleted

Answer 5

Orderdate is declared as DATETIME and yes it does contain the date and time.  The reason I need the start date and end date in the format of '2017-01-31 00:00' Is I need to match up with reports within an ERP system that compares dates in that way.

P.S.  This is a 2008 SQL server.  No FORMAT() FUNCTION

---

Duckkiller53

Answer 6

You can use CAST directly:

--select getdate()  --2017-03-07 00:00:00.000
SELECT cast(@cur_date + @time_start  as datetime) AS 'cur_date_start'  

When you work with datetime, you may need to work with two functions: dateadd and datediff.

Answer 7

Deleted

Answer 8

The reason I need the start date and end date in the format of '2017-01-31 00:00'

No you don't. Datetime (and other non-string based datatypes) are stored in a binary format that is not readable by a human. Do not confuse presentation with data. And you can easily swap format with convert using the appropriate style. I gave you the link to a fuller discussion - which seems beyond hope. To use convert rather than format (which I'll argue you don't need to do in the first place):

select GETDATE() as x, 
format(getdate(), 'yyyyMMdd') + N' 23:59:00.000' as y, 
cast(format(getdate(), 'yyyyMMdd') + N' 23:59:00.000' as datetime) as z 
;

select GETDATE() as x, 
convert(nchar(8), getdate(), 112) + N' 23:59:00.000' as y, 
cast(convert(nchar(8), getdate(), 112)  + N' 23:59:00.000' as datetime) as z 
;

Again - stop thinking "HOW" and start thinking "WHAT". Do not choose a path prematurely.

Answer 9

Hi Duckkiller53,

When converting datetime from character string, better to use the ISO 8601 format. The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].