|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date/String formatstandard format to do this and I need to do this in Sql as there is no client program here. SELECT DateBeg,Convert(varChar(2),DatePart(mm,DateBeg)) + Convert(varChar(2),DatePart(dd,DateBeg)) + Convert(varChar(4),DatePart(yyyy,DateBeg)) as PayDate Gives me this: DateBeg PayDate ------------------------------- --------------- 2004-05-07 00:00:00.000 572004 What I am trying to get is "05072004"? I assume this is because, even though I asked for "mm" or "dd instead of "m" or "d", it will become an integer first which will drop the leading 0 then is converted to a string. How do I get this mmddyyyy result? Thanks, Tom SELECT datebeg,
REPLACE(CONVERT(VARCHAR,datebeg,101),'/','') AS silly_date_format FROM T ; -- David Portas SQL Server MVP -- Is that an opinion :)
Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:BrSdnTJTktJuv7beRVn-sQ@giganews.com... > SELECT datebeg, > REPLACE(CONVERT(VARCHAR,datebeg,101),'/','') AS silly_date_format > FROM T ; > > -- > David Portas > SQL Server MVP > -- > > That did work, however - silly format or not :)
Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23FtFeExuFHA.2840@TK2MSFTNGP10.phx.gbl... > Is that an opinion :) > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:BrSdnTJTktJuv7beRVn-sQ@giganews.com... >> SELECT datebeg, >> REPLACE(CONVERT(VARCHAR,datebeg,101),'/','') AS silly_date_format >> FROM T ; >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> > > > What I am trying to get is "05072004"? Are all of your users really going to understand that this is May 7th? I think some will think July 5th. Why not use an unambiguous format? Anyway, what you are looking for is this, I put it into a subquery so you only have to type/perform the convert once. SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) FROM table) x Whoops
SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) FROM (SELECT DateBeg, dt = CONVERT(CHAR(8), DateBeg, 112) FROM table) x Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... >> What I am trying to get is "05072004"? > > Are all of your users really going to understand that this is May 7th? I > think some will think July 5th. Why not use an unambiguous format? > > Anyway, what you are looking for is this, I put it into a subquery so you > only have to type/perform the convert once. > > SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) > FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) > FROM table) x > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message This is a one time conversion.news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... >> What I am trying to get is "05072004"? The CSV file requires that the date is in this format. I have no choice in that. This is why there is no client software. I am just doing this with a very large script and then saving it as a tabbed CSV file. Tom Show quote > > Are all of your users really going to understand that this is May 7th? I > think some will think July 5th. Why not use an unambiguous format? > > Anyway, what you are looking for is this, I put it into a subquery so you > only have to type/perform the convert once. > > SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) > FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) > FROM table) x > Out of curiosity, why does the CSV file "require" the date in that format?
I know this is semantics, but the CSV file itself does not have any demands for formatting - you can create a CSV file with the date formatted just about any way you like and it will still be a CSV file. Unlike a SQL table, there are no built in constraints on file formatting (at least not in this case). My guess is that someone is going to open this CSV file in a different application, such as Excel, or they are going to import it into a different system. So ultimately, there is client software involved, otherwise the format would be irrelevant. Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:ed3DeFxuFHA.3764@TK2MSFTNGP09.phx.gbl... > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... >>> What I am trying to get is "05072004"? > > This is a one time conversion. > > The CSV file requires that the date is in this format. I have no choice > in that. This is why there is no client software. I am just doing this > with a very large script and then saving it as a tabbed CSV file. > > Tom >> >> Are all of your users really going to understand that this is May 7th? I >> think some will think July 5th. Why not use an unambiguous format? >> >> Anyway, what you are looking for is this, I put it into a subquery so you >> only have to type/perform the convert once. >> >> SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) >> FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) >> FROM table) x >> > > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message Actually, I think the file is used by many different companies and they want news:eizWBNxuFHA.2960@tk2msftngp13.phx.gbl... > Out of curiosity, why does the CSV file "require" the date in that > format? I know this is semantics, but the CSV file itself does not have > any demands for formatting - you can create a CSV file with the date > formatted just about any way you like and it will still be a CSV file. > Unlike a SQL table, there are no built in constraints on file formatting > (at least not in this case). > > My guess is that someone is going to open this CSV file in a different > application, such as Excel, or they are going to import it into a > different system. So ultimately, there is client software involved, > otherwise the format would be irrelevant. to make sure they don't have to worry about 50 different date formats, so they just chose as their format. There are no constraints, but they still need to make sure the date is valid. Tom Show quote > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:ed3DeFxuFHA.3764@TK2MSFTNGP09.phx.gbl... >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >> message news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... >>>> What I am trying to get is "05072004"? >> >> This is a one time conversion. >> >> The CSV file requires that the date is in this format. I have no choice >> in that. This is why there is no client software. I am just doing this >> with a very large script and then saving it as a tabbed CSV file. >> >> Tom >>> >>> Are all of your users really going to understand that this is May 7th? >>> I think some will think July 5th. Why not use an unambiguous format? >>> >>> Anyway, what you are looking for is this, I put it into a subquery so >>> you only have to type/perform the convert once. >>> >>> SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) >>> FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) >>> FROM table) x >>> >> >> > > Exactly - and the format you are asking about does *not* ensure the date is
valid - it is ambiguous. Also, as I mentioned, your statement about there being no client applications involved is not completely accurate - how are these "many different companies" going to work with the file if they do not open it or import it into some system? Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:u$kQ3UxuFHA.2504@tk2msftngp13.phx.gbl... > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message > news:eizWBNxuFHA.2960@tk2msftngp13.phx.gbl... > > Out of curiosity, why does the CSV file "require" the date in that > > format? I know this is semantics, but the CSV file itself does not have > > any demands for formatting - you can create a CSV file with the date > > formatted just about any way you like and it will still be a CSV file. > > Unlike a SQL table, there are no built in constraints on file formatting > > (at least not in this case). > > > > My guess is that someone is going to open this CSV file in a different > > application, such as Excel, or they are going to import it into a > > different system. So ultimately, there is client software involved, > > otherwise the format would be irrelevant. > > Actually, I think the file is used by many different companies and they want > to make sure they don't have to worry about 50 different date formats, so > they just chose as their format. > > There are no constraints, but they still need to make sure the date is > valid. > > Tom > > > > "tshad" <tscheider***@ftsolutions.com> wrote in message > > news:ed3DeFxuFHA.3764@TK2MSFTNGP09.phx.gbl... > >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > >> message news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... > >>>> What I am trying to get is "05072004"? > >> > >> This is a one time conversion. > >> > >> The CSV file requires that the date is in this format. I have no choice > >> in that. This is why there is no client software. I am just doing this > >> with a very large script and then saving it as a tabbed CSV file. > >> > >> Tom > >>> > >>> Are all of your users really going to understand that this is May 7th? > >>> I think some will think July 5th. Why not use an unambiguous format? > >>> > >>> Anyway, what you are looking for is this, I put it into a subquery so > >>> you only have to type/perform the convert once. > >>> > >>> SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) > >>> FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) > >>> FROM table) x > >>> > >> > >> > > > > > > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message I agree.news:%23$MCdkyuFHA.3548@tk2msftngp13.phx.gbl... > Exactly - and the format you are asking about does *not* ensure the date > is > valid - it is ambiguous. But I don't control the format. I am putting the data into an application we are using. They have told me what the csv file must look like and what the date format is. I assume they will check the format when they read the CSV file and reject any lines that have invalid dates. > It is not that many companies are going to work with the file. Many > Also, as I mentioned, your statement about there being no client > applications involved is not completely accurate - how are these "many > different companies" going to work with the file if they do not open it or > import it into some system? companies use this program and authors of the program didn't want to set up a CSV file with many different formats, so they are telling these companies what date format to use. Tom Show quote > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:u$kQ3UxuFHA.2504@tk2msftngp13.phx.gbl... >> "Jeremy Williams" <jeremydw***@netscape.net> wrote in message >> news:eizWBNxuFHA.2960@tk2msftngp13.phx.gbl... >> > Out of curiosity, why does the CSV file "require" the date in that >> > format? I know this is semantics, but the CSV file itself does not have >> > any demands for formatting - you can create a CSV file with the date >> > formatted just about any way you like and it will still be a CSV file. >> > Unlike a SQL table, there are no built in constraints on file >> > formatting >> > (at least not in this case). >> > >> > My guess is that someone is going to open this CSV file in a different >> > application, such as Excel, or they are going to import it into a >> > different system. So ultimately, there is client software involved, >> > otherwise the format would be irrelevant. >> >> Actually, I think the file is used by many different companies and they > want >> to make sure they don't have to worry about 50 different date formats, so >> they just chose as their format. >> >> There are no constraints, but they still need to make sure the date is >> valid. >> >> Tom >> > >> > "tshad" <tscheider***@ftsolutions.com> wrote in message >> > news:ed3DeFxuFHA.3764@TK2MSFTNGP09.phx.gbl... >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >> >> message news:ORWLpivuFHA.2792@tk2msftngp13.phx.gbl... >> >>>> What I am trying to get is "05072004"? >> >> >> >> This is a one time conversion. >> >> >> >> The CSV file requires that the date is in this format. I have no > choice >> >> in that. This is why there is no client software. I am just doing > this >> >> with a very large script and then saving it as a tabbed CSV file. >> >> >> >> Tom >> >>> >> >>> Are all of your users really going to understand that this is May >> >>> 7th? >> >>> I think some will think July 5th. Why not use an unambiguous format? >> >>> >> >>> Anyway, what you are looking for is this, I put it into a subquery so >> >>> you only have to type/perform the convert once. >> >>> >> >>> SELECT DateBeg, PayDate = RIGHT(dt, 4) + LEFT(dt, 4) >> >>> FROM (SELECT dt = CONVERT(CHAR(8), DateBeg, 112) >> >>> FROM table) x >> >>> >> >> >> >> >> > >> > >> >> > > replace(convert(varchar,datebeg,101), '/', '')
tshad wrote: Show quote >I have a date that I need to get into a mmddyyyy format. There is no >standard format to do this and I need to do this in Sql as there is no >client program here. > >SELECT DateBeg,Convert(varChar(2),DatePart(mm,DateBeg)) + >Convert(varChar(2),DatePart(dd,DateBeg)) + >Convert(varChar(4),DatePart(yyyy,DateBeg)) as PayDate > >Gives me this: >DateBeg PayDate >------------------------------- --------------- >2004-05-07 00:00:00.000 572004 > >What I am trying to get is "05072004"? > >I assume this is because, even though I asked for "mm" or "dd instead of "m" >or "d", it will become an integer first which will drop the leading 0 then >is converted to a string. > >How do I get this mmddyyyy result? > >Thanks, > >Tom > > > > > > "Trey Walpole" <treypoNOle@comSPAMcast.net> wrote in message That does the job.news:uRLOykvuFHA.3152@TK2MSFTNGP12.phx.gbl... > replace(convert(varchar,datebeg,101), '/', '') Thanks, Tom Show quote > > tshad wrote: > >>I have a date that I need to get into a mmddyyyy format. There is no >>standard format to do this and I need to do this in Sql as there is no >>client program here. >> >>SELECT DateBeg,Convert(varChar(2),DatePart(mm,DateBeg)) + >>Convert(varChar(2),DatePart(dd,DateBeg)) + >>Convert(varChar(4),DatePart(yyyy,DateBeg)) as PayDate >> >>Gives me this: >>DateBeg PayDate >>------------------------------- --------------- >>2004-05-07 00:00:00.000 572004 >> >>What I am trying to get is "05072004"? >> >>I assume this is because, even though I asked for "mm" or "dd instead of >>"m" or "d", it will become an integer first which will drop the leading 0 >>then is converted to a string. >> >>How do I get this mmddyyyy result? >> >>Thanks, >> >>Tom >> >> >> >> >> Check out Convert in BOL or try this:
select DateBeg, substring(Convert(Char(12), DateBeg, 101) , 1,2)+ substring9Convert(Char(12), DateBeg, 101) , 4, 2)+ substring(Convert(Char(12), DateBeg, 101) , 7, 10) Perayu Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:O1F9WbvuFHA.2792@tk2msftngp13.phx.gbl... >I have a date that I need to get into a mmddyyyy format. There is no >standard format to do this and I need to do this in Sql as there is no >client program here. > > SELECT DateBeg,Convert(varChar(2),DatePart(mm,DateBeg)) + > Convert(varChar(2),DatePart(dd,DateBeg)) + > Convert(varChar(4),DatePart(yyyy,DateBeg)) as PayDate > > Gives me this: > DateBeg PayDate > ------------------------------- --------------- > 2004-05-07 00:00:00.000 572004 > > What I am trying to get is "05072004"? > > I assume this is because, even though I asked for "mm" or "dd instead of > "m" or "d", it will become an integer first which will drop the leading 0 > then is converted to a string. > > How do I get this mmddyyyy result? > > Thanks, > > Tom > > > >  
Other interesting topics
|
|||||||||||||||||||||||
