asked
Datetime formatted date converted to '1900-01-01' in temp table
In the attached stored procedure, I am inserting the following values into a temporary table: [Branch Abbreviation, TransactionDate, Status, Count]. Ultimately stored procedure calculates the total number of transactions by status (Hold shelf, In transit, checkout), (Checkin), (Shelf), (Purge, cataloging, repair or missing parts, review). I get strange results if I were to report on them together. When I insert the values into the temporary file the date is written as 1900-01-13 00:00:00.000 instead of the current date (2014-12-01 10:49:49.777). When I execute the queries outside of the stored procedure the correct date is returned: (2014-12-01 10:49:49.777). when the contents of the temporary table are written out, I use DATEPART(MONTH, WL.TDate) to extract the Month value.[link text][1] The date format for the column "TDate" in the temporary table is datetime. What do I need to do to return the correct/current date? I am using SQLServer 2008R2 Enterprise edition. Thank you. [1]: /storage/temp/1891-itemrecordhistory+date+format+for+temp+table.txt
As you said (and I confirmed by checking your code), you are storing the transaction month in your temp table. That will just be an integer from 1 to 12. Since you are storing it as a datetime, however, it adds the integer value to 1/1/1900. So, if the transaction date was in December, it would return 1/13/1900. My question to you is why are you storing just the month but using a datetime data type? I just thought about it some more. Are you trying to report just the month and year of each transaction and store that (using the 1st of the month) as a date in the temp table? If so, I suggest using the following code in your procedure, replacing @t with whatever variable or column needed. DATEADD(day, 1-DATEPART(day, @t), @t) PS datetime is essentially deprecated in SQL Server since version 2008. Use datetime2 and related types like date and time for new code. In your case, it sounds like the "date" type would work best. Edit after latest comments: If you only want the month number in the report, change the data type to int. However, you may need the year and month to avoid confusion. In that case, you could use the code I posted earlier to store the first of the month for each transaction or store the year and month in separate columns or store a single 6-character column with year and month as YYYYMM. Here are the options I am proposing along with sample output of each. Code: -- report the month number SELECT t.name, DATEPART(month, t.modify_date) FROM sys.tables t WHERE t.name = 'test'; -- report the month and year in separate columns SELECT t.name, DATEPART(month, t.modify_date), DATEPART(year, t.modify_date) FROM sys.tables t WHERE t.name = 'test'; -- report the year and month in a single column SELECT t.name, CONVERT(char(6), t.modify_date, 112) FROM sys.tables t WHERE t.name = 'test'; -- report the first of the month of the transaction -- i.e. transaction on 12/25/2014 is reported as 12/1/2014 SELECT t.name, DATEADD(day, 1-DATEPART(day, t.modify_date), t.modify_date) FROM sys.tables t WHERE t.name = 'test'; Output: Test 12 Test 12 2014 Test 201412 Test 12/01/2014
@jonlellelid If @Tom Staab's answer helped you, please mark it as accepted (there is a check mark near the top-left of the answer). You are the OP, so you are the only one outside of the site moderators who can determine if the answer has helped you to resolve the problem, and if it has then marking it as accepted might help someone who may have a similar question in the future.
Following previous examples? Learning SQL by trial and error! So it would be better to store the full date in the table, then in the report section only use the month?
When I use the IRH.TransactionDate with out the Month, the totals are not grouped by category so I get ARL 2014-12-31 14:46:23.330 Hold Shelf 1 ARL 2014-12-02 17:28:21.447 In-Transit 1 ARL 2014-12-04 10:24:57.053 In-Transit 1 ARL 2014-12-05 14:06:49.090 In-Transit 1 ARL 2014-12-06 14:27:00.917 In-Transit 1 ARL 2014-12-08 20:37:34.493 In-Transit 1 ARL 2014-12-09 13:37:44.150 In-Transit 1 ARL 2014-12-12 11:08:43.060 In-Transit 1 in the temp file when what I want is Branch Month, Status Total ARL, 12, Hold Shelf, 20 ARL, 12, In-Transit, 45 ARL, 12, Checked-out, 75 etc. So how might I proceed with this? Thanks
@jonlellelid As @Tom Staab already explained in his answer, you cannot use just the month part of the source column and store it in the datetime column of the temp table. Also, group by DATEPART(MONTH, IRH.TransactionDate) is not safe if the data spans over more than one year because such grouping will group records from different years but same month. One way to get the data you need is to bunch any IRH.TransactionDate values for the same month into one value. This will allow you to get the grouping correctly. To return, say, 1st of the month from any given datetime input for that month, you can use this: dateadd(month, datediff(month, 0, IRH.TransactionDate), 0) In other words, if you change the line in your proc in the insert into #workload part reading **DATEPART(MONTH, IRH.TransactionDate) AS Month** to read **dateadd(month, datediff(month, 0, IRH.TransactionDate), 0)** and also make the same change in the group by clause then you get the data you need. Here is the link which explains the [datetime storage details][1] you might find helpful. Oleg [1]:
https://ask.sqlservercentral.com/questions/17549/php-with-mssql-strtotime-with-mssql-datetime-colum.html
THANKS so much for your assistance Tom and Oleg. I got it to work!!
15 People are following this question.
