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 |
|
cbrya
Starting Member
1 Post |
 Posted - 2005-08-12 : 15:07:24
|
| I am trying to figure out how to get a date code in a format we require for our database.We're doing a Select into from another table into another table. Part of the select will be taking a column in the select DsDate and creating three new columns for the target table.So we want it like this:1. DsYear - 2005 (or whatever the year is from the date which is easy to extract with DatePart).2. DsQuarter. We don't just want the quarter here. If Q2 2004, we want this to be 20041. Not sure how to do this, any help would be appreciated. Right now I'm doing this so I'm not sure if this will work or not: DATEPART(yy,d.DsDate) + DATEPART(qq,d.DsDate) AS DsQuarter So not sure if concatenation will work like this when concatenating results from two DatePArt calls.3. This is the one driving my post. For DsMonth we want it to be like YYYYMM. So for Feb. 2004 we want 200402. Normally the DatePArt(mm, DsDate) will give us 2 not 02 in this case, so how can we get 02? And assuming the concatenation will work as mentioned in #2 above then I could just get the year and concatenate with the month. The month has to be 2 digits even for months prior to October since 20041 is different from 200401. These will end up being foreign keys (not enforced) back to a DateCode table in another database and 20041 would return back a Description of Q1 2004.Don't ask why we're doing this.... I had another idea but this was the way I've been told to do it. Thanks for your help. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-12 : 16:25:10
|
| To do the concatenation that you want, you have to cast each part as char or varchar, otherwise SQL will attempt to add the two numbers instead of concatenate them.To get the leading zero, you'll have to concatenate that yourself, so it would look something like:SELECT CAST(DATEPART(yyyy, DsDate) AS char(4)) + RIGHT('0' + CAST(DATEPART(m, DsDate) AS varchar(2)), 2)---------------------------EmeraldCityDomains.com |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-13 : 01:39:46
|
| This also will give the same outputSelect left(convert(varchar,getdate(),112),6)Which Front End Application are you using?If possible, do this kind of formation thereMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Datepart and month formatting