Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>

Complex substring query and value conversion help...

Ok, I have to convert the dollar values of some of my fields in a
query, if the field to the left (in the left column) has a word that
part of the word has "debit" in it. Like this:
if(t5.description = "TransferDebit")
{
DECLARE @.Num1 int
SET @.Num1 = t6.amount
SELECT -@.Num1
}
Thanks,
TrintIt is not clear from your post what you are exactly trying to do. What are
t5 and t6 in your sample code? Are you trying to extract a portion of the
string value in some column?
Perhaps you can use SUBSTRING function to extract a part of a string. To
find if "debit" is a part of the value in description column, you can use
CHARINDEX or PATINDEX. Details of all these functions, with examples, can be
found in SQL Server Books Online.
Anith|||Please provide actual DDL, sample data, and expected results
[http://www.aspfaq.com/etiquette.asp?id=5006]. how are T5 and T6 related? we
need to see the structure and data you are actually working with.
Without better specification, I am going to work from the premise that all
this information is actually in a single table. Do you want to actually
change the values as they are stored, or do you just want to display them in
a negative format?
If the former:
UPDATE T5 SET amount = -amount WHERE description LIKE '%Debit%'
If the latter:
SELECT
CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount END AS
Amount,
<other columns to display...>
FROM T5
"trint" <trinity.smith@.gmail.com> wrote in message
news:1122047085.356296.239470@.o13g2000cwo.googlegroups.com...
> Ok, I have to convert the dollar values of some of my fields in a
> query, if the field to the left (in the left column) has a word that
> part of the word has "debit" in it. Like this:
> if(t5.description = "TransferDebit")
> {
> DECLARE @.Num1 int
> SET @.Num1 = t6.amount
> SELECT -@.Num1
> }
> Thanks,
> Trint
>|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
Actually, here is the code:
SELECT t1.MemberId, t1.PeriodID,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount) END) AS
Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount END) AS
QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id
WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID
thanks,
Trint