jymmealey
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>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))
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))
ASKER
mherchl,
I ran the following and got an error:
SELECT CONVERT(datetime, CONVERT(varchar,'10/26/200
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
I ran the following and got an error:
SELECT CONVERT(datetime, CONVERT(varchar,'10/26/200
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
set @datenew = convert(datetime, convert(varchar,@olddate, 112) + ' 23:59')