SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 3 numeric fields into 1 date field issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vqcheese
Starting Member

USA
19 Posts

Posted - 12/31/2012 :  11:28:54  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
336 Posts

Posted - 12/31/2012 :  11:50:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/31/2012 :  11:55:24  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 12/31/2012 :  11:56:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/31/2012 :  12:03:16  Show Profile  Reply with Quote
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

Edited by - jimf on 12/31/2012 12:04:09
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
336 Posts

Posted - 12/31/2012 :  12:05:08  Show Profile  Reply with Quote
'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.

Edited by - ScottPletcher on 12/31/2012 12:30:27
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/31/2012 :  16:48:12  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 12/31/2012 16:49:11
Go to Top of Page

vqcheese
Starting Member

USA
19 Posts

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/02/2013 :  08:29:29  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 01/02/2013 :  13:12:34  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 01/02/2013 :  16:54:53  Show Profile  Reply with Quote
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

USA
19 Posts

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/07/2013 :  08:05:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 01/07/2013 :  20:32:53  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 01/08/2013 :  00:08:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Constraint Violating Yak Guru

USA
336 Posts

Posted - 01/08/2013 :  11:09:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000