Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using COVERT function to convert DATE to DATETIME

Author  Topic 

Rahul Raj
Starting Member

41 Posts

Posted - 2014-12-01 : 11:44:11
Hi All,

I am using DATEADD function to calculate a DATETIME value prior to 3 months of the present value.
value2='2014-12-01'
select @value1=CONVERT (VARCHAR(100),@value2,126)

the value of value1 variable is 2014-12-01 00:00:00.000. Can someone please suggest if instead of zeroes can I get high values as 1998-01-02 23:59:59.999.

or can I add 1 day to the input value2 so that I get the output as 2014-12-02 00:00:00.000'

Thanks in Advance!

Thanks in Advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-01 : 12:00:46
select left(CONVERT (VARCHAR(100),@value2,126), 8) + ' 23:59:59.999'
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-12-01 : 14:18:34
Be careful with using strings to perform any time of date comparisons. What it sounds like you are doing is setting up filtering based on date values - if so, the model is:

WHERE datecolumn >= @startDate
AND datecolumn < @endDate

For your end date - if you want to be able to pass in the last day of the month (for example) and make sure you capture everything on that day regardless of the time component...

SET @endDate = CAST(@parmDate AS date); -- Strip the time...

WHERE datecolumn >= @startDate
AND datecolumn < DATEADD(day, 1, @endDate)

Hope this helps give you some ideas...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-12-22 : 08:02:42
I think you want to do range search based on datetime values. Refer this post for more information http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -