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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 3 numeric fields into 1 date field issue

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 parts
The 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 parameter

Cast(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)
Go to Top of Page

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 date


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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?
Go to Top of Page

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?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page

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 parts
The 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 parameter

Cast(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
Go to Top of Page

vqcheese
Starting Member

19 Posts

Posted - 2013-01-02 : 07:53:00
Thanks guys, JIMf, i dont see your example on here?
Go to Top of Page

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 like
CONVERT(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 as
a date in sql, you can't change the way SQL displays it by default, but you can change the way
it 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)

Jim

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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 = @productionDate
and 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 processing
Cannon read the next data row for data set dsSealline
The conversion of a char data type to a datetime data type resulted in an out of range datetime value.

Go to Top of Page

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 = @productionDate
and 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 processing
Cannon read the next data row for data set dsSealline
The 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
Go to Top of Page

vqcheese
Starting Member

19 Posts

Posted - 2013-01-03 : 09:40:47
That was my issue, had data of 00 00 12. Thanks.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-07 : 08:05:01
quote:
Originally posted by vqcheese

That was my issue, had data of 00 00 12. Thanks.


You need to exclude all rows where DAY/MONTH/YEAR values are wrong.

You may be interested to know various methods to do this
http://beyondrelational.com/modules/2/blogs/70/posts/15788/10-ways-to-simulate-dateserial-function.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-08 : 00:08:00
Here are the unambiguous formats
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -