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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

Construct Date in SQL

Hello all,

I have a Stored Proc that take a date as a parameter.  I would like to create a variable that contains the same date as the passed value but I want to change the time to 11:59pm.

For example if the proc gets the date '10/25/2006 8:00 am', I would like my varaible to say "10/25/2006 11:59 pm'.
If the proc gets a date without time I still need it to work, so '10/26/2006' would be '10/26/2006 11:59 pm'

Thanks
Jym
0
jymmealey
Asked:
jymmealey
1 Solution
 
mherchlCommented:
declare @datenew datetime
set @datenew = convert(datetime, convert(varchar,@olddate, 112) + ' 23:59')
0
 
GokulmCommented:
You can construct the date as follows and return

SELECT  CONVERT(char(10), GETDATE(), 101) + ' 11:59 PM'

This will retun a character

SELECT CAST( CONVERT(char(10), GETDATE(), 101) + ' 11:59 PM' AS datetime)

This will return a datetime
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>For example if the proc gets the date '10/25/2006 8:00 am', I would like my varaible to say "10/25/2006 11:59 pm'.
I guess because you want to get all before 10/26/2006...

where yourfield < dateadd(day, 1, convert(datetime, convert (varchar(10), @parameter, 101), 101))
0
 
jymmealeyAuthor Commented:
mherchl,

I ran the following and got an error:

SELECT CONVERT(datetime, CONVERT(varchar,'10/26/2006 8:00 am', 112) + ' 23:59')

Error was
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I assume this is because of the type 112, it is expecting ISO?  

Ran Gokulm's query and it worked like expected.

Thanks
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now