SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Format Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/15/2013 :  10:43:49  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/15/2013 :  11:09:45  Show Profile  Reply with Quote
Hi Lamprey its going back into a crm tool and for any date function it also needs to have a time portion

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 08/15/2013 :  11:26:40  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/15/2013 :  11:29:59  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 08/15/2013 :  11:34:36  Show Profile  Reply with Quote
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;
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/15/2013 :  11:44:44  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/15/2013 :  12:00:13  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 08/15/2013 :  12:02:24  Show Profile  Reply with Quote
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;
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/15/2013 :  12:09:28  Show Profile  Reply with Quote
IM using sql server 2008 r2
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 08/18/2013 :  11:51:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You may need to check the length of data along with ISDATE function http://beyondrelational.com/modules/2/blogs/70/posts/10807/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000