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.
Author |
Topic |
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2014-07-10 : 16:25:13
|
A client is sending us a date format of a 1-2 digit month(they drop the leading 0), 2 digit day, 4 digit year: 7102014. I'm loading this into an etl table as a string, but then I need to insert it into a date column. I have a working SQL query that will convert it to a date if the month is 1 or 2 digits, but I'm wondering if there's a better (shorter) way I can handle this:declare @Date varchar(10)set @Date = '12152022'--set @Date = '6012014'select convert(date,RIGHT(LTRIM(RTRIM(@Date)),4) + SUBSTRING(LTRIM(RTRIM('0' + @Date)),LEN('0' + @Date)-7,2) + SUBSTRING(LTRIM(RTRIM(@Date)),LEN(@Date)-5,2))Thanks |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-10 : 16:43:04
|
select case when len(@Date)=8 then @Date else '0' + @Date endTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-10 : 17:04:29
|
[code]DECLARE @Sample TABLE ( Data VARCHAR(10) NOT NULL );INSERT @Sample ( Data )VALUES ('12152022'), ('6012014');-- SwePesoSELECT Data, CONVERT(DATETIME, STUFF(STUFF(Data, LEN(Data) - 3, 0, '/'), LEN(Data) - 5, 0, '/'), 101) AS theResultFROM @Sample;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2014-07-11 : 09:10:37
|
SwePeso,That worked, and 50 characters shorter than what I had. I haven't seen the STUFF command before so thanks.Still trying to get the client to send us CCYYMMDD format but this will work nicely until they switch (if ever).Thanks again. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-07-15 : 08:45:33
|
Another method select dateadd(month,(data/1000000)-1, dateadd(year,data%10000-1900,0)+(data/10000%100-1)) from @sampleMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|