| Author |
 Topic  |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
 Posted - 08/15/2013 : 10:43:49
|
Hey guys
I need some help
I have the following column called “[OTLT_FRST_POST_DT] “ Data type “Varchar (50)”
The format the date is in is “20110621”
However i need the date format to be “2011-06-21T23:01”
would appreciate any help available |
|
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 08/15/2013 : 11:08:36
|
| According to your sample data, 20110621 is just a date. Where does the time portion come from? |
Edited by - Lamprey on 08/15/2013 11:08:55 |
 |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 08/15/2013 : 11:09:45
|
Hi Lamprey its going back into a crm tool and for any date function it also needs to have a time portion
|
|
|
|
James K
Flowing Fount of Yak Knowledge
3769 Posts |
Posted - 08/15/2013 : 11:26:40
|
SELECT CONVERT(VARCHAR(16),CAST('20110621' AS DATETIME),127)I have the same question as Lamprey. Since the source data does not have the time portion, how do you get a time portion into there? The query I posted simply assumes the time is midnight - i.e., 00:00 |
|
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 08/15/2013 : 11:29:59
|
Hi James K
thank you for posting a soultion
I have around 300rows, so i dont want to do indvidual casts per date.
Anyway that can be adapted to a case statement |
|
|
|
James K
Flowing Fount of Yak Knowledge
3769 Posts |
Posted - 08/15/2013 : 11:34:36
|
I only meant that as an example. If your data is in a table use it like this:SELECT CONVERT(VARCHAR(16),CAST(YourYYYYMMDDDateColumn AS DATETIME),127)
FROM YourTable; |
|
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 08/15/2013 : 11:44:44
|
James k
One step ahead of you ,
when i done that i got the following error messsage
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
query is
select
#emax.MID_Internal as FDMSAccountNo,
#emax.Internal as External_ID,
Parentsfid,
[OTLT_ACCT_ID],
[OTLT_MRCH_NM] as [DBA Name],
[LEGL_NM] as [Legal Name],
[OTLT_BLLN_ADDR_FRST_LINE_TX] as Street,
[OTLT_BLLN_ADDR_SCND_LINE_TX] as [MM3-DBA-ADDR2],
[OTLT_BLLN_ADDR_SCND_LINE_TX] as [MM3-DBA-ADDR4],
[OTLT_BLLN_ADDR_CITY_NM] as City,
[OTLT_BLLN_ADDR_PSTL_CD] as Postalcode,
[OTLT_BLLN_ADDR_CTRY_CD] as Country,
[OTLT_BLLN_ADDR_CTRY_CD] as Phone,
CONVERT(VARCHAR(16),CAST([OTLT_FRST_POST_DT] AS DATETIME),127) as test,
COALESCE([OTLT_FRST_POST_DT],' ') as Open_Date,
[OTLT_BLLN_ADDR_CTRY_CD] as Cancel_Date,
COALESCE([OTLT_FRST_POST_DT],' ') as First_Post_Date,
[OTLT_BLLN_ADDR_CTRY_CD] as Last_Post_Date,
#emax.Account_ID
Into #EmaxAddress
from #emax
inner join [FDMS].[dbo].[stg_EMAXS_Merchant] ON right(#emax.MID_Internal,9) = right([FDMS].[dbo].[stg_EMAXS_Merchant].OTLT_ACCT_ID,9)
--- Building Parent Hierarchy --
---Outlet to chain --
inner join (
select distinct substring([OTLT_CHAN_CRPR_ID],2,12) as Parentsfid ,
[OTLT_ACCT_ID] as child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000'
and [OTLT_ACCT_ID] <> substring([OTLT_CHAN_CRPR_ID],2,12)
-- Outlet to Corp--
union all
select distinct substring([OTLT_CHAN_CRPR_ID],2,12) as Parentsfid
,[OTLT_ACCT_ID] as child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000'
and substring([OTLT_CHAN_CRPR_ID],2,12) = '000000000000'
and [OTLT_ACCT_ID] <>substring([OTLT_CHAN_CRPR_ID],2,12)
-- Outlet to Agent --
union all
select distinct substring([OTLT_CHAN_AGNT_ID],2,12) as Parentsfid
,[OTLT_ACCT_ID] as child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) = '000000000000'
and substring([OTLT_CHAN_CRPR_ID],2,12) = '000000000000'
and [OTLT_ACCT_ID] <> substring([OTLT_CHAN_AGNT_ID],2,12)
-- Chain to Corp--
union all
select distinct substring([OTLT_CHAN_CRPR_ID],2,12) as Parentsfid
,substring([OTLT_CHAN_CRPR_ID],2,12) as child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000'
and substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000'
-- Chain to agent--
union all
select distinct substring([OTLT_CHAN_AGNT_ID],2,12) as Parentsfid
,substring([OTLT_CHAN_CRPR_ID],2,12) as Child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000'
and substring([OTLT_CHAN_CRPR_ID],2,12) = '000000000000'
-- Corp to agent --
union all
select Distinct substring([OTLT_CHAN_AGNT_ID],2,12) as Parentsfid
,substring([OTLT_CHAN_CRPR_ID],2,12) as Child
FROM [FDMS].[dbo].[stg_EMAXS_Merchant]
where substring([OTLT_CHAN_CRPR_ID],2,12) <> '000000000000')
x
on x.child = [FDMS].[dbo].[stg_EMAXS_Merchant].[OTLT_ACCT_ID]
select *
from #EmaxAddress
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 08/15/2013 : 12:00:13
|
quote:
Originally posted by masond
James k
One step ahead of you ,
when i done that i got the following error messsage
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
That means you have bad data. That is why you should always use proper data types. You can try the ISDATE function to see if any of the date values are not convertable.
What version of SQL Server are you using? If 2012 there are some other parsing functions that may work better an ISDATE. |
|
|
|
James K
Flowing Fount of Yak Knowledge
3769 Posts |
Posted - 08/15/2013 : 12:02:24
|
See which rows are giving you problems using this query. If it returns any rows at all, inspect the [OTLT_FRST_POST_DT] column in those rows.SELECT
*
FROM
#emax
inner join [FDMS].[dbo].[stg_EMAXS_Merchant] ON right(#emax.MID_Internal,9) = right([FDMS].[dbo].[stg_EMAXS_Merchant].OTLT_ACCT_ID,9)
WHERE ISDATE(([OTLT_FRST_POST_DT] ) = 0; |
|
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 08/15/2013 : 12:09:28
|
| IM using sql server 2008 r2 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22791 Posts |
|
| |
Topic |
|
|
|
Date Format Help
