Set Hours in DateTime variable

[pdbowling]

Hello, everyone.

I have a parameter coming in to a stored procedure.

ALTER PROCEDURE [dbo].[udp_GetPriority]
@CurrentDateTime DATETIME

It's defaulted to 12:00AM as the hour, and I'd like to set the time in the T-SQL statement before running my query.

I didn't see an obvious method jump out as I was digging through the help files on DateTime types.
I saw where you can pull things out with DatePart() but not the syntax or methods to write back.

Any suggestions would be great.

(PS: I don't have the source code of the calling program or I'd just have it send in the time with the hour set as needed.)
Thanks, All.
Patrick

 

[ESquared]

if you're going to override the value you're given, just do:

set @currentdatetime = getdate()

If you trust that the variable will always have today's date in it, override it. If you don't trust it will always have today's date in it, ever adding today's time to it is problematic.

 

[bborissov]

DECLARE @Test DATETIME
SET @Test = '20071022'
SELECT @Test
 -- You should get 22 Oct 2007 12:00:00 AM as a result

SELECT DATEADD(ss, 18*3600+27*60+31, @Test)
 -- You should get 22 Oct 2007 06:27:31 PM as a result

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Boris. You missed one.

DECLARE @Test DATETIME
SET @Test = '20071022'

SELECT @Test
 -- You should get 22 Oct 2007 12:00:00 AM as a result

[!]SELECT @Test + '6:27:31 PM'[/!]
[green] -- You should get 22 Oct 2007 06:27:31 PM as a result[/green]

SELECT DATEADD(ss, 18*3600+27*60+31, @Test)
 -- You should get 22 Oct 2007 06:27:31 PM as a result
[code]

This will ONLY work if the time portion of the datetime is midnight.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

George,
No, I didn't miss that, but I don't know why I prefer to add seconds to date to get the datetime. Maybe because when I started with SQL Server I always get error or wrong result when I tried to convert a string to datetime
But you are right, that possibility should be mentioned.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[pdbowling]

Many thanks to all of you.
bborissov,
gmmastros,
ESquared

This will resolve my difficulties.
Patrick

 

[itprsn]

I have a date/time field with the following data as an example: '4/8/2008 12:03:14 AM'. Need the correct syntax in SQL to pull out just the date. Can anybody help? Thanks...

 

[k01]

Hi,

You can get only date part by this SQL.

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CAST('4/8/2008 12:03:14 AM' AS DATETIME))))

Koichi

http://aboutabi.blogspot.com/

(SQLServer & Cognos Tips)

 

[noodles1]

dateadd(day, 0, datediff(day, 0, YourDateColumnOrVar))

 

[SQLSister]

Read this FAQ

http://www.tek-tips.com/faqs.cfm?fid=6628

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

noodles1, I think there's an error in your expression. Reverse the 2nd and 3rd operands in your dateadd:

dateadd(day, datediff(day, 0, YourDateColumnOrVar), 0)

 

[noodles1]

ESquared,

I think you will find that either form works.

 

[ESquared]

<thinking...> yes you're right. Silly me.

But wait, that exposes that all we're doing with the dateadd is converting the date serial number back to a datetime data type, because with your syntax you're logically adding 0 days to a date instead of adding a number of days to date 0. In fact, the following all work with your syntax:

dateadd(mm, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(mi, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(yy, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(hh, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ss, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ms, 0, datediff(day, 0, YourDateColumnOrVar))
dateadd(ww, 0, datediff(day, 0, YourDateColumnOrVar))

(assuming I'm remembering the codes for each time unit right)

To me this is now a final and compelling argument why this construct is better avoided in favor of either the operand order I suggested or an explicit conversion:

convert(datetime, datediff(day, 0, YourDateColumnOrVar))

I can even see an argument for:

convert(datetime, datediff(day, '1900-01-01', YourDateColumnOrVar))