Author |
Topic |
vqcheese
Starting Member
19 Posts |
Posted - 2012-12-31 : 11:28:54
|
I have a table that has 3 numeric fields that are actually date partsThe 3 columns are sl_prodmonth, sl_prodday, sl_prodyear and each columns values are for example 12, 31, 2012. My issue is how can i combined these 3 fields into 1 date field. I have been using this but i want to be able to use this date in my report project for a parameterCast(sl_prodmonth as varchar) + '/' + cast(sl_prodday as varchar) + '/' + cast(sl_prodyear as varchar) as productiondate,any help would be great. |
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-31 : 11:50:32
|
cast(sl_prodyear as char(4)) + right('0' + cast(sl_prodmonth as varchar(2)), 2) + right('0' + cast(sl_prodday as varchar(2)), 2) |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-31 : 11:55:24
|
This doesn't format it like Scott's does, but it can also be used as dateJimEveryday I learn something that somebody else already knew |
|
|
vqcheese
Starting Member
19 Posts |
Posted - 2012-12-31 : 11:56:05
|
that makes my data look like 20121230 is there a way to get it to be like a normal date format in sql? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-31 : 12:03:16
|
There is no normal date format in SQL. Dates are integers, but you can make them into strings that have a more familiar look to them. As I said in my post, mine doesn't format things like Scott's. If you wanted things formatted like Scott's, then you should use Scott's. Are you adding a new column to your table that would be the desired dated field?JimEveryday I learn something that somebody else already knew |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-31 : 12:05:08
|
'YYYYMMDD' is the only 100% safe date format in SQL. And it will always implicitly convert correctly to a date when required.SELECT DATEADD(DAY, 7, '20121230')You can also explicitly CAST it to an actual date/datetime if you need to, of course.The date format you get back from SQL is NOT how SQL stores it, it's just the default display style. Thus, what you see is not the "standard", or internal, format. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-12-31 : 16:48:12
|
quote: Originally posted by vqcheese I have a table that has 3 numeric fields that are actually date partsThe 3 columns are sl_prodmonth, sl_prodday, sl_prodyear and each columns values are for example 12, 31, 2012. My issue is how can i combined these 3 fields into 1 date field. I have been using this but i want to be able to use this date in my report project for a parameterCast(sl_prodmonth as varchar) + '/' + cast(sl_prodday as varchar) + '/' + cast(sl_prodyear as varchar) as productiondate,any help would be great.
This will convert them to a DATETIME:select CombinedDatetime = dateadd(month,(12*sl_prodyear)-22801+sl_prodmonth,sl_prodday-1)from MyTable CODO ERGO SUM |
|
|
vqcheese
Starting Member
19 Posts |
Posted - 2013-01-02 : 07:53:00
|
Thanks guys, JIMf, i dont see your example on here? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-02 : 08:29:29
|
Can't believe I didn't post my answer! I did something likeCONVERT(char(10),(sl_prodyear*10000) + (sl_prodmonth*100) + sl_prodday).Whichever way you choose to go, the thing to remember is that if you storing something asa date in sql, you can't change the way SQL displays it by default, but you can change the wayit looks by explicitly converting the date to a string. If you are adding an extra column,definitely make it a date data type and let the front-end format it for display.E.g.select convert(char(10),current_timestamp,101)JimJimEveryday I learn something that somebody else already knew |
|
|
vqcheese
Starting Member
19 Posts |
Posted - 2013-01-02 : 13:12:34
|
Thanks, but im still having an issue with my end result is. No matter what format i get for a date im getting this error in my reporting services when running the report.I want to use this new formatted date as a parameter in my report so like this in my where clause Where Productiondate = @productionDateand then in my paramaters i set that to a date field and not a string and i get the calendar option to pick the date. but if i leave string it works, but if i put as a date i get this error when running the report:An Error Occured during local report processingCannon read the next data row for data set dsSeallineThe conversion of a char data type to a datetime data type resulted in an out of range datetime value. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-01-02 : 16:54:53
|
quote: Originally posted by vqcheese Thanks, but im still having an issue with my end result is. No matter what format i get for a date im getting this error in my reporting services when running the report.I want to use this new formatted date as a parameter in my report so like this in my where clause Where Productiondate = @productionDateand then in my paramaters i set that to a date field and not a string and i get the calendar option to pick the date. but if i leave string it works, but if i put as a date i get this error when running the report:An Error Occured during local report processingCannon read the next data row for data set dsSeallineThe conversion of a char data type to a datetime data type resulted in an out of range datetime value.
You have an invalid combination of year, month, and day that cannot be converted to a datetime. You will have to find the bad data and correct it if you can.That is the biggest problem with storing a date like that. Unless you do a lot of work to make sure that you have a valid date stored you will have problems like this.CODO ERGO SUM |
|
|
vqcheese
Starting Member
19 Posts |
Posted - 2013-01-03 : 09:40:47
|
That was my issue, had data of 00 00 12. Thanks. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-07 : 20:32:53
|
quote: Originally posted by ScottPletcher 'YYYYMMDD' is the only 100% safe date format in SQL.
I do not think that is true statement; There is also ISO8601:yyyy-mm-ddThh:mi:ss.mmm (no spaces)yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-01-08 : 11:09:03
|
quote: Originally posted by Lamprey
quote: Originally posted by ScottPletcher 'YYYYMMDD' is the only 100% safe date format in SQL.
I do not think that is true statement; There is also ISO8601:yyyy-mm-ddThh:mi:ss.mmm (no spaces)yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
That's true. I should have said it's the only standard format that's 100% safe. Most people don't ever use the "T" version. |
|
|
|