Concatenate Date & Time

I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
nvarchar(8) type. I have 2 problems.
1. I need a way to concatenate the 2 fields into 1 datetime field with a
select statement
2.In my example data below, you can see that dtTime is in a "military" time
format. Is there a way within SQL to convert it to a normal time format with
the AM/PM?
Any ideas or help would be greatly appreciated.
Example of Data ******************
dtDate dtTime
---
3/14/2006 12:00:00 AM 01:21:57
3/15/2006 12:00:00 AM 14:42:53Why are these separate? Anyway, try this, untested...
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
Of course, this will only work if all of your dtTime values are valid times.
Since you chose NVARCHAR for some reason, this is an extra hassle to
validate / constrain.
"Scott Bailey" <sbailey@.mileslumber.com> wrote in message
news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
>nvarchar(8) type. I have 2 problems.
> 1. I need a way to concatenate the 2 fields into 1 datetime field with a
> select statement
> 2.In my example data below, you can see that dtTime is in a "military"
> time format. Is there a way within SQL to convert it to a normal time
> format with the AM/PM?
> Any ideas or help would be greatly appreciated.
>
> Example of Data ******************
> dtDate dtTime
> ---
> 3/14/2006 12:00:00 AM 01:21:57
> 3/15/2006 12:00:00 AM 14:42:53
>|||Your code returns just the date part like:
2006-03-14
Can you modify it to display the date and time? Also, can you have the whole
result converted to datetime format?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||One last note, I didn't create this db, I just inherited it or I would never
have split the Date and Time into different fields.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||thank you. it works.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ug$RJsAZGHA.1228@.TK2MSFTNGP02.phx.gbl...
>I was basing it on this:
> SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
> Which returns:
> --
> 2006-04-19 7:40:33 PM
> Maybe it will work better like this:
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
> I would put this into a view so you don't have to repeat this calculation
> everywhere.
> If that still doesn't yield the correct results, then please post DDL and
> sample data so we can actually try and reproduce your issue.
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
>