DateTime SQL Server

Hi there!

I terribly need help in understanding what this error means - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Basically, I have the date value from calendar control and inserting it into SQL Server 2000. Any ideas??

Thank you.Read the rror again :-)

Then look at your date. The formatting is wrong and SQL Server tries to mmisinterpret the fields. When it "f%*ks up" with month and date, the date can be 25 - this is out of range for a month.

You need to make sure the datetime you enter is in the correct form.

::I have the date value from calendar control and inserting it into SQL Server 2000. Any
::ideas??

Not matching cultures.

datetime

Hello,
I have a table with a colume named orderTime.
Because of the size of the DB, I need to make sure all the data is
stream lined. This means that if I only need the time, I need to just
store the time. Unfortunately, datetime stores 1900-01-01 as a data
with the time which is a string from a VB app.
Anyway to insert just the time? I could change the column to a string,
but I rather not do that because I'm not sure if I can order by time
properly latter.> Anyway to insert just the time?
No, SQL Server does not have a date only or time only datatype. But can't
you just always ignore 1900-01-01?
Some workarounds here, but they're not pretty.
http://www.aspfaq.com/2206
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> No, SQL Server does not have a date only or time only datatype. But can't
> you just always ignore 1900-01-01?
> Some workarounds here, but they're not pretty.
> http://www.aspfaq.com/2206
Aaron,
Thanks. Yes, I can ignore them. The issue though is that because the
sheer amount of data I'm storing, I need to only insert data that is
needed. I guess I will just have to work around this. Thanks.|||> Thanks. Yes, I can ignore them. The issue though is that because the
> sheer amount of data I'm storing, I need to only insert data that is
> needed.
? If you store a date in a DATETIME or SMALLDATETIME, regardless if you
include all information, it still occupies 4 or 8 bytes respectively. So
"not inserting" the portion you don't need is not going to save you any
space.|||I don't loose any accuracy if I were to store that time as a char(8) and
then just do a CAST?
Which will take up less space?
I found this in Books Online,
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Doesn't seem to give a definitive answer.|||SMALLDATETIME (4 bytes) will take up less space than a CHAR(8) (8 bytes).
Plus, you lose effectiveness of indexes and have to convert for every single
query.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Won Lee" <noemail@.nospam.com> wrote in message
news:#CfX$vIAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> I don't loose any accuracy if I were to store that time as a char(8) and
> then just do a CAST?
> Which will take up less space?
> I found this in Books Online,
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
> Doesn't seem to give a definitive answer.
>

datetime

Hello,
I have a table with a colume named orderTime.
Because of the size of the DB, I need to make sure all the data is
stream lined. This means that if I only need the time, I need to just
store the time. Unfortunately, datetime stores 1900-01-01 as a data
with the time which is a string from a VB app.
Anyway to insert just the time? I could change the column to a string,
but I rather not do that because I'm not sure if I can order by time
properly latter.> Anyway to insert just the time?
No, SQL Server does not have a date only or time only datatype. But can't
you just always ignore 1900-01-01?
Some workarounds here, but they're not pretty.
http://www.aspfaq.com/2206
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> No, SQL Server does not have a date only or time only datatype. But can't
> you just always ignore 1900-01-01?
> Some workarounds here, but they're not pretty.
> http://www.aspfaq.com/2206
Aaron,
Thanks. Yes, I can ignore them. The issue though is that because the
sheer amount of data I'm storing, I need to only insert data that is
needed. I guess I will just have to work around this. Thanks.|||> Thanks. Yes, I can ignore them. The issue though is that because the
> sheer amount of data I'm storing, I need to only insert data that is
> needed.
? If you store a date in a DATETIME or SMALLDATETIME, regardless if you
include all information, it still occupies 4 or 8 bytes respectively. So
"not inserting" the portion you don't need is not going to save you any
space.|||I don't loose any accuracy if I were to store that time as a char(8) and
then just do a CAST?
Which will take up less space?
I found this in Books Online,
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Doesn't seem to give a definitive answer.|||SMALLDATETIME (4 bytes) will take up less space than a CHAR(8) (8 bytes).
Plus, you lose effectiveness of indexes and have to convert for every single
query.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Won Lee" <noemail@.nospam.com> wrote in message
news:#CfX$vIAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> I don't loose any accuracy if I were to store that time as a char(8) and
> then just do a CAST?
> Which will take up less space?
> I found this in Books Online,
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
> Doesn't seem to give a definitive answer.
>

Dates in a DTS

I am running a DTS package and the dates are hard coded...which means I have
to change the date each time I go to run it.
I would like to change it so that it does it automaticaly...so when it runs,
it takes one off the system date and transfers the data...so it only
transfers the previous days data.
Can anyone help, please.
Here is the package details
****************************************
***
SELECT IDTYPP, IDINVN, IDLINE, IDCUNO,
TRIM(IDPRDC) AS IDPRDC, TRIM(IDUNIT) AS IDUNIT,
OLCONV,
CASE
WHEN IDTYPP = 2 THEN -OLOQTY
ELSE OLOQTY
END OLOQTY,
CASE
WHEN IDTYPP = 2 THEN -IDQTY
ELSE IDQTY
END IDQTY,
IDSALP - (IDSALP * IDDCPR / 100) AS IDNPRC,
CASE
WHEN IDTYPP = 1 THEN IDAMOU * IDEXR3
ELSE -IDAMOU * IDEXR3
END IDAMOU,
CASE
WHEN IDTYPP = 1 THEN IDCOSP * IDEXR3
ELSE -IDCOSP * IDEXR3
END IDCOSP,
TRIM(IDCAGR) AS IDCAGR, TRIM(IDSALE) AS IDSALE, IDIDAT,
CASE
WHEN IDTYPP = 1 THEN IDQTY * IDCOSP * IDEXR3
ELSE -IDQTY * IDCOSP * IDEXR3
END IDLCVA
FROM LTR403FLL.SR3ISD D
LEFT OUTER JOIN LTR403FLL.SRBSOL O
ON D.IDORNO = O.OLORNO
AND D.IDOLIN = O.OLLINE
WHERE IDPERI >= 200411
AND IDIDAT >= 20050301 AND IDIDAT < 20050307
****************************************
******
The line
AND IDIDAT >= 20050301 AND IDIDAT < 20050307
is the one I would like to change to retrieve the data from the previous daySet up global variables for the dates you want to assign on the fly.
You can then set them in ActiveX scripts or using a dynamic properties task.
Simon Worth
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:B1EE60B2-2E10-46DC-891C-FFAB658F6562@.microsoft.com...
> I am running a DTS package and the dates are hard coded...which means I
have
> to change the date each time I go to run it.
> I would like to change it so that it does it automaticaly...so when it
runs,
> it takes one off the system date and transfers the data...so it only
> transfers the previous days data.
> Can anyone help, please.
> Here is the package details
> ****************************************
***
> SELECT IDTYPP, IDINVN, IDLINE, IDCUNO,
> TRIM(IDPRDC) AS IDPRDC, TRIM(IDUNIT) AS IDUNIT,
> OLCONV,
> CASE
> WHEN IDTYPP = 2 THEN -OLOQTY
> ELSE OLOQTY
> END OLOQTY,
> CASE
> WHEN IDTYPP = 2 THEN -IDQTY
> ELSE IDQTY
> END IDQTY,
> IDSALP - (IDSALP * IDDCPR / 100) AS IDNPRC,
> CASE
> WHEN IDTYPP = 1 THEN IDAMOU * IDEXR3
> ELSE -IDAMOU * IDEXR3
> END IDAMOU,
> CASE
> WHEN IDTYPP = 1 THEN IDCOSP * IDEXR3
> ELSE -IDCOSP * IDEXR3
> END IDCOSP,
> TRIM(IDCAGR) AS IDCAGR, TRIM(IDSALE) AS IDSALE, IDIDAT,
> CASE
> WHEN IDTYPP = 1 THEN IDQTY * IDCOSP * IDEXR3
> ELSE -IDQTY * IDCOSP * IDEXR3
> END IDLCVA
> FROM LTR403FLL.SR3ISD D
> LEFT OUTER JOIN LTR403FLL.SRBSOL O
> ON D.IDORNO = O.OLORNO
> AND D.IDOLIN = O.OLLINE
> WHERE IDPERI >= 200411
> AND IDIDAT >= 20050301 AND IDIDAT < 20050307
> ****************************************
******
> The line
> AND IDIDAT >= 20050301 AND IDIDAT < 20050307
> is the one I would like to change to retrieve the data from the previous
day

Dates

Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
all kinds of problems fiquiring out how to properly use Dates in the
reporting tool. For example, How can I have a report give the user the
option to view the report by: year, month, day, date ranges, even by quarter
of a choosen year.
Can someone point me in the right direction?
Thanks,
SeanThis is your lucky day! :^)
I just completed this same thing. Here's a snippet of T-SQL to do that. I
have two variables, @.Begin_Date and @.End_Date that I use in my select to set
the date range for the report. In my Report I have text fields for Begin and
End date, but I also have a drop down that lets the user select "last 7 days"
Last 30 Days", Last Year, etc. If they select a value from the drop down, it
overrides the Begin Date text box. The dropdown sets integer values; 0 is
all periods, 1 is "Since Yesterday" etc. Here are the "allowable" values.
The ones marked ** are not supported by my code yet--I'll get around to them
later.
-- Allowable Values are:
--
-- 0 or blank All periods
-- 1 Today **
-- 2 Since beginning of Yesterday **
-- 3 Since beginning of This Week **
-- 4 Since beginning of Last Week **
-- 5 Within the last 7 days
-- 6 Since beginning of This Month **
-- 7 Since beginning of Last Month **
-- 8 Within the last 30 days
-- 9 Since beginning of This Year **
-- 10 Since beginning of Last Year **
-- 11 Within the last year
-- 12 Within the last 2 Years
-- ** Reserved - Not supported for COI yet.
You can add logic for This Quarter and such as well, using the same logic as
the code below. I seed some variables, and the goal of this SELECT is to set
@.Begin_Date and @.End_Date so that when I query for records, I can do a where
clause like:
WHERE
Form.Last_Modified_Date >= @.Begin_Date
AND
Form.Last_Modified_Date <= @.End_Date
Here's the code that sets @.Begin_Date and @.End_Date:
SELECT
-- Stamp the date and time
,@.TodayDateTime = GETDATE()
-- Get today as of 12:00am
,@.TodayDate = CAST (
CAST(DATEPART(year, @.TodayDateTime) AS varchar) +
'/' +
CAST(DATEPART(month, @.TodayDateTime) AS varchar)
+
'/' +
CAST(DATEPART(day, @.TodayDateTime) AS varchar)
AS datetime
)
-- Calculate the Begin_Date based on the period
,@.Begin_Date = CASE (@.Period)
WHEN 5 THEN DATEADD ( Day , -7, @.TodayDate)
WHEN 8 THEN DATEADD ( Day , -30, @.TodayDate)
WHEN 11 THEN DATEADD ( Year , -1, @.TodayDate)
WHEN 12 THEN DATEADD ( Year , -2, @.TodayDate)
ELSE CASE (SELECT ISDATE(@.Begin_Period))
WHEN 1 THEN CAST(@.Begin_Period AS datetime)
ELSE CAST('01/01/1900' AS datetime)
END
END
-- Dates count from 12:00AM, so we actually want End_Date at 12:00AM
the following day.
,@.End_Date = CASE (SELECT ISDATE(@.End_Period))
WHEN 1 THEN DATEADD ( Day , 1, CAST(@.End_Period AS datetime))
ELSE @.TodayDateTime
END
Regards,
Mike Sharp
"Sean" wrote:
> Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
> all kinds of problems fiquiring out how to properly use Dates in the
> reporting tool. For example, How can I have a report give the user the
> option to view the report by: year, month, day, date ranges, even by quarter
> of a choosen year.
> Can someone point me in the right direction?
> Thanks,
> Sean|||rdcpro,
This looks excellent. How to implement it?
Example:
SELECT * FROM TESTDB
Where do I put all of your code? As a sperate Dataset?
Sorry for being stupid.
Sean
"rdcpro" wrote:
> This is your lucky day! :^)
> I just completed this same thing. Here's a snippet of T-SQL to do that. I
> have two variables, @.Begin_Date and @.End_Date that I use in my select to set
> the date range for the report. In my Report I have text fields for Begin and
> End date, but I also have a drop down that lets the user select "last 7 days"
> Last 30 Days", Last Year, etc. If they select a value from the drop down, it
> overrides the Begin Date text box. The dropdown sets integer values; 0 is
> all periods, 1 is "Since Yesterday" etc. Here are the "allowable" values.
> The ones marked ** are not supported by my code yet--I'll get around to them
> later.
> -- Allowable Values are:
> --
> -- 0 or blank All periods
> -- 1 Today **
> -- 2 Since beginning of Yesterday **
> -- 3 Since beginning of This Week **
> -- 4 Since beginning of Last Week **
> -- 5 Within the last 7 days
> -- 6 Since beginning of This Month **
> -- 7 Since beginning of Last Month **
> -- 8 Within the last 30 days
> -- 9 Since beginning of This Year **
> -- 10 Since beginning of Last Year **
> -- 11 Within the last year
> -- 12 Within the last 2 Years
> -- ** Reserved - Not supported for COI yet.
> You can add logic for This Quarter and such as well, using the same logic as
> the code below. I seed some variables, and the goal of this SELECT is to set
> @.Begin_Date and @.End_Date so that when I query for records, I can do a where
> clause like:
> WHERE
> Form.Last_Modified_Date >= @.Begin_Date
> AND
> Form.Last_Modified_Date <= @.End_Date
>
> Here's the code that sets @.Begin_Date and @.End_Date:
>
> SELECT
> -- Stamp the date and time
> ,@.TodayDateTime = GETDATE()
> -- Get today as of 12:00am
> ,@.TodayDate => CAST (
> CAST(DATEPART(year, @.TodayDateTime) AS varchar) +
> '/' +
> CAST(DATEPART(month, @.TodayDateTime) AS varchar)
> +
> '/' +
> CAST(DATEPART(day, @.TodayDateTime) AS varchar)
> AS datetime
> )
> -- Calculate the Begin_Date based on the period
> ,@.Begin_Date => CASE (@.Period)
> WHEN 5 THEN DATEADD ( Day , -7, @.TodayDate)
> WHEN 8 THEN DATEADD ( Day , -30, @.TodayDate)
> WHEN 11 THEN DATEADD ( Year , -1, @.TodayDate)
> WHEN 12 THEN DATEADD ( Year , -2, @.TodayDate)
> ELSE CASE (SELECT ISDATE(@.Begin_Period))
> WHEN 1 THEN CAST(@.Begin_Period AS datetime)
> ELSE CAST('01/01/1900' AS datetime)
> END
> END
> -- Dates count from 12:00AM, so we actually want End_Date at 12:00AM
> the following day.
> ,@.End_Date => CASE (SELECT ISDATE(@.End_Period))
> WHEN 1 THEN DATEADD ( Day , 1, CAST(@.End_Period AS datetime))
> ELSE @.TodayDateTime
> END
>
> Regards,
> Mike Sharp
>
> "Sean" wrote:
> > Now, by no means am I a SQL Guru. I know the basics. I seem to be having a
> > all kinds of problems fiquiring out how to properly use Dates in the
> > reporting tool. For example, How can I have a report give the user the
> > option to view the report by: year, month, day, date ranges, even by quarter
> > of a choosen year.
> >
> > Can someone point me in the right direction?
> >
> > Thanks,
> >
> > Sean