converting to date time with only date and hour passed in

Question

I am using SQL server, i want to insert datetime into my datetime column. The parameters i recieved as follows

@hour int,
@date varchar(10) -- format YYYY-MM-DD

How do I convert to this datetime format:

YYYY-MM-DD HH:MM:SS

MM will be 00 as we only consider the hour.
HH:MM will be in 24 hours format

Example :

@date = '2014-10-2'
@hour = '8'

Should be converted to

2014-10-2 08:00:00

How do I do this?

[Not a duplicate, but should be interesting](http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) — Diligent Key Presser, Oct 14 '14 at 04:18
Don't you mean that you want this result? `2014-10-02 08:00:00` — Troy Gizzi, Oct 14 '14 at 04:35
Also, you might want to clarify whether `@hour` is supposed to be an `int` or a `varchar`. — Troy Gizzi, Oct 14 '14 at 04:35

Pரதீப் · Accepted Answer · 2014-10-14T04:42:09.467

4

I consider that both the variables are of varchar type since you enclosed both the variables with quotes

  SELECT CONVERT(DATETIME, @date + ' ' + convert(varchar(10),@hour) + ':00:00')

edited Oct 14 '14 at 04:42

answered Oct 14 '14 at 04:30

Pரதீப்

Okay but i kept on getting `Conversion failed when converting the varchar value '2014-10-20 ' to data type int.` – ETAN Oct 14 '14 at 04:39
@edward use `CONVERT(VARCHAR, @hour)` – Diligent Key Presser Oct 14 '14 at 04:41
@MadSorcerer - varchar inside convert without size is not a good practice. Try to mention the size – Pரதீப் Oct 14 '14 at 04:45

score 0 · Answer 2 · answered Oct 14 '14 at 07:18

0

here is one another way to do this:-

declare @hour int=8,
@date varchar(10)='2014-10-2'
select dateadd(hh,@hour,CONVERT(datetime,@date))

answered Oct 14 '14 at 07:18

user3864233

2 Answers2