Date Format Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Format Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-15 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-15 : 11:08:36

According to your sample data, 20110621 is just a date. Where does the time portion come from?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-15 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 11:26:40

[code]SELECT CONVERT(VARCHAR(16),CAST('20110621' AS DATETIME),127)[/code]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 - 2013-08-15 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 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 - 2013-08-15 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-15 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 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 - 2013-08-15 : 12:09:28

IM using sql server 2008 r2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 11:51:02

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources