I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...
> Can somebody tell me what I am doing wrong in my code?
A guess only...but..
> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?
Thursday, March 29, 2012
Converting DateTime
Hello Everyone and thanks for your help in advance. I have an application that inserts a variety of values into a SQL Server database. Among the columns are three DateTime values. I have code working properly on my test server, but when I port the identical code to my production database, I get the following error:
Arithmetic overflow error converting expression to data type datetime. The statement has been terminated
When I remove any type of insert involving date, the application works. I have tried the date in various formats, for instance "09/12/2001" and "20010912", but still get the same error. Obviously, there must be differences in the SQL Servers, but I have never run into this problem before and the current server is running many applications involving dates. I haven't got a clue as to how to solve this problem. Any help is greatly appreciated. Thanks.hi kmcnet,
i had the same problem. but i was using query in code at that time but now i am using stored procedure and i can pass the date value now. no problem with that. anyway that was really really annoying thing. at one point i had used varchar to store date in database which i used to change to datetime datatype before displaying. but i think that wasn't a proper solution.sqlsql
Converting DateTime
Hi,
When I open a tabe in the SQL enterprise manager I see the Timestamp Field
in this Format :
24/01/2005 16:45:00
When I open a tabe in the SQL enterprise manager I see the Timestamp Field
in this Format :
24/01/2005 16:45:00
However when I'm using the Query analyzer or other SQL Client I see the
Timestamp Field in this Format :
2005-01-24 16:44:59.997
Does Anybody know what to do in order to display the Timestamp Field in
first Format (24/01/2005 16:45:00) ?
Please Advise,
Yariv
--
Message posted via http://www.sqlmonster.comLook up "Date Time String Transformation" in SQL Server BOL
"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com|||"Yariv via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:732a9f6af71c48d6ab1ff539f0fe3351@.SQLMonster.c om...
> Hi,
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
> Please Advise,
> Yariv
> --
> Message posted via http://www.sqlmonster.com
Check out CONVERT() in Books Online. But you need to remember that MSSQL
stores datetime values in an internal format, and each client (including EM
and QA) decides how to display them. See here for more details:
http://www.karaszi.com/sqlserver/info_datetime.asp
Simon|||Yariv via SQLMonster.com (forum@.SQLMonster.com) writes:
> When I open a tabe in the SQL enterprise manager I see the Timestamp Field
> in this Format :
> 24/01/2005 16:45:00
> However when I'm using the Query analyzer or other SQL Client I see the
> Timestamp Field in this Format :
> 2005-01-24 16:44:59.997
> Does Anybody know what to do in order to display the Timestamp Field in
> first Format (24/01/2005 16:45:00) ?
Under Tool->Options->Connections, check the third checkbox, "Use
regional settings...".
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Converting dates.
Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way
for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way
for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?
It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)
i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?
SELECT * FROM Table WHERE ISDATE(DateCol)=0
??
SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return
what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2
mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed
unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...
:)|||sounds like the attitude of a certain large software company which shall remain nameless...
yea, they drill it into you, it takes a while to feel clean again. :)
did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.
How much rows are you having in your table..?
Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )
Consider the points given below, remember you didn't provide enough information...
1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).
2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.
3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?
Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )
Consider the points given below, remember you didn't provide enough information...
1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).
2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.
3. Inform your higher authority & update your table, this way your new data will not be wrong.
I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.
There are few ways to update your DATETIME columns...
1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.
Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).
By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.
for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way
for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?
It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)
i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?
SELECT * FROM Table WHERE ISDATE(DateCol)=0
??
SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return
what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2
mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed
unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...
:)|||sounds like the attitude of a certain large software company which shall remain nameless...
yea, they drill it into you, it takes a while to feel clean again. :)
did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.
How much rows are you having in your table..?
Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )
Consider the points given below, remember you didn't provide enough information...
1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).
2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.
3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?
Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )
Consider the points given below, remember you didn't provide enough information...
1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).
2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.
3. Inform your higher authority & update your table, this way your new data will not be wrong.
I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.
There are few ways to update your DATETIME columns...
1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.
Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).
By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.
Converting dates into floats
I have an odd database that has a date stored in a float field. The
dates originate in an external database, which is imported record-by-
record, with data cleanup and conversion, into SQL Server. We do many
of these sorts of conversions, but normally they are put into a
datetime field instead of a float.
Everything seems to work perfectly under VB. However, I am in the
process of converting from VB into SQL for a variety of reasons. This
floatdate is causing a problem, as it is always off by two days. Let
me give you an example...
On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
read in the date from the external database, which stores it as a
string: "20080605". Here is the code I use to convert it...
Function RealDate(Datestring) As Date
Dim Yr As String
Dim Mth As String
Dim Dy As String
On Error GoTo notadate
Yr = Left(Datestring, 4)
Mth = Right(Left(Datestring, 6), 2)
Dy = Right(Datestring, 2)
RealDate = Mth & "/" & Dy & "/" & Yr
Exit Function
notadate:
RealDate = 1 / 1 / 1900
End Function
So far so good. I then put that result, a VB Date, directly into the
float field in the database. If I then read that back out in VB and
cast it to a date (which is automatic if you want) I get back the same
value.
However, when I do this in SQL, I get a _slightly_ different date:
cast(price2 as datetime) as expiry
returns 2008-06-07 00:00:00.000
It's off by _two days_. At first I thought this was an epoch issue.
Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
Server uses 1/1/1900. Is this understanding correct? If so, how is it
that the resulting date in SQL is only off by two days, and not 70
years?
MauryMaury,
SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
somewhere along the line) that Visual Basic's zero day is 1899/12/30. (I
believe it was supposed to be 1899/12/31, so that makes two mistakes, one
for each day that your calculation is off.)
I am relying on memory since I cannot find the reference right now.
RLF
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:e8ffe61e-6b41-4b1c-b1ce-f25890d22ece@.d1g2000hsg.googlegroups.com...
>I have an odd database that has a date stored in a float field. The
> dates originate in an external database, which is imported record-by-
> record, with data cleanup and conversion, into SQL Server. We do many
> of these sorts of conversions, but normally they are put into a
> datetime field instead of a float.
> Everything seems to work perfectly under VB. However, I am in the
> process of converting from VB into SQL for a variety of reasons. This
> floatdate is causing a problem, as it is always off by two days. Let
> me give you an example...
> On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
> read in the date from the external database, which stores it as a
> string: "20080605". Here is the code I use to convert it...
> Function RealDate(Datestring) As Date
> Dim Yr As String
> Dim Mth As String
> Dim Dy As String
> On Error GoTo notadate
> Yr = Left(Datestring, 4)
> Mth = Right(Left(Datestring, 6), 2)
> Dy = Right(Datestring, 2)
> RealDate = Mth & "/" & Dy & "/" & Yr
> Exit Function
> notadate:
> RealDate = 1 / 1 / 1900
> End Function
> So far so good. I then put that result, a VB Date, directly into the
> float field in the database. If I then read that back out in VB and
> cast it to a date (which is automatic if you want) I get back the same
> value.
> However, when I do this in SQL, I get a _slightly_ different date:
> cast(price2 as datetime) as expiry
> returns 2008-06-07 00:00:00.000
> It's off by _two days_. At first I thought this was an epoch issue.
> Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
> Server uses 1/1/1900. Is this understanding correct? If so, how is it
> that the resulting date in SQL is only off by two days, and not 70
> years?
> Maury|||On Apr 29, 3:16=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
> somewhere along the line) that Visual Basic's zero day is 1899/12/30. =A0(=I
> believe it was supposed to be 1899/12/31, so that makes two mistakes, one
> for each day that your calculation is off.)
LOL! Ok, that DOES explain it. I'll just remember to -2 from now on.
Maury
dates originate in an external database, which is imported record-by-
record, with data cleanup and conversion, into SQL Server. We do many
of these sorts of conversions, but normally they are put into a
datetime field instead of a float.
Everything seems to work perfectly under VB. However, I am in the
process of converting from VB into SQL for a variety of reasons. This
floatdate is causing a problem, as it is always off by two days. Let
me give you an example...
On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
read in the date from the external database, which stores it as a
string: "20080605". Here is the code I use to convert it...
Function RealDate(Datestring) As Date
Dim Yr As String
Dim Mth As String
Dim Dy As String
On Error GoTo notadate
Yr = Left(Datestring, 4)
Mth = Right(Left(Datestring, 6), 2)
Dy = Right(Datestring, 2)
RealDate = Mth & "/" & Dy & "/" & Yr
Exit Function
notadate:
RealDate = 1 / 1 / 1900
End Function
So far so good. I then put that result, a VB Date, directly into the
float field in the database. If I then read that back out in VB and
cast it to a date (which is automatic if you want) I get back the same
value.
However, when I do this in SQL, I get a _slightly_ different date:
cast(price2 as datetime) as expiry
returns 2008-06-07 00:00:00.000
It's off by _two days_. At first I thought this was an epoch issue.
Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
Server uses 1/1/1900. Is this understanding correct? If so, how is it
that the resulting date in SQL is only off by two days, and not 70
years?
MauryMaury,
SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
somewhere along the line) that Visual Basic's zero day is 1899/12/30. (I
believe it was supposed to be 1899/12/31, so that makes two mistakes, one
for each day that your calculation is off.)
I am relying on memory since I cannot find the reference right now.
RLF
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:e8ffe61e-6b41-4b1c-b1ce-f25890d22ece@.d1g2000hsg.googlegroups.com...
>I have an odd database that has a date stored in a float field. The
> dates originate in an external database, which is imported record-by-
> record, with data cleanup and conversion, into SQL Server. We do many
> of these sorts of conversions, but normally they are put into a
> datetime field instead of a float.
> Everything seems to work perfectly under VB. However, I am in the
> process of converting from VB into SQL for a variety of reasons. This
> floatdate is causing a problem, as it is always off by two days. Let
> me give you an example...
> On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
> read in the date from the external database, which stores it as a
> string: "20080605". Here is the code I use to convert it...
> Function RealDate(Datestring) As Date
> Dim Yr As String
> Dim Mth As String
> Dim Dy As String
> On Error GoTo notadate
> Yr = Left(Datestring, 4)
> Mth = Right(Left(Datestring, 6), 2)
> Dy = Right(Datestring, 2)
> RealDate = Mth & "/" & Dy & "/" & Yr
> Exit Function
> notadate:
> RealDate = 1 / 1 / 1900
> End Function
> So far so good. I then put that result, a VB Date, directly into the
> float field in the database. If I then read that back out in VB and
> cast it to a date (which is automatic if you want) I get back the same
> value.
> However, when I do this in SQL, I get a _slightly_ different date:
> cast(price2 as datetime) as expiry
> returns 2008-06-07 00:00:00.000
> It's off by _two days_. At first I thought this was an epoch issue.
> Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
> Server uses 1/1/1900. Is this understanding correct? If so, how is it
> that the resulting date in SQL is only off by two days, and not 70
> years?
> Maury|||On Apr 29, 3:16=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
> somewhere along the line) that Visual Basic's zero day is 1899/12/30. =A0(=I
> believe it was supposed to be 1899/12/31, so that makes two mistakes, one
> for each day that your calculation is off.)
LOL! Ok, that DOES explain it. I'll just remember to -2 from now on.
Maury
Converting Dates as paramters
Hey guys,
Hoping i can get some help with this one..
Problem:
Data source has a datetime format as YYYYMMDD
I would like to have my users enter a date in the format of DD/MM/YYYY and in the background have it convert to the YYYYMMDD so i can filter my data according to the data
ANy ideas on how i will do this?
thanks
scotty
Use Format function and convert the date parameter to required format.
Format(Parameters!prmStartDate.Value, "yyyy-MM-dd")
converting date/time to just date?
I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
What I want is to do the following:
Say you have these records:
person | date-time
---+--------
jim | 06/02/2004 00:05:52
jim | 06/02/2004 05:06:21
jim | 06/02/2004 05:46:21
jim | 06/15/2004 11:26:21
jim | 06/15/2004 11:35:21
dave | 06/04/2004 09:35:21
dave | 06/04/2004 11:05:21
dave | 06/06/2004 10:34:21
dave | 06/08/2004 11:37:21
I'd like the results to count how many days and return
person | days
---+---
jim | 2
dave | 3
How would I do this?
--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@.12jabber.com <--jabber IM ]On Tue, 22 Jun 2004 15:27:52 -0000, Sugapablo wrote:
>I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
>What I want is to do the following:
>Say you have these records:
>person | date-time
>---+--------
>jim | 06/02/2004 00:05:52
>jim | 06/02/2004 05:06:21
>jim | 06/02/2004 05:46:21
>jim | 06/15/2004 11:26:21
>jim | 06/15/2004 11:35:21
>dave | 06/04/2004 09:35:21
>dave | 06/04/2004 11:05:21
>dave | 06/06/2004 10:34:21
>dave | 06/08/2004 11:37:21
>I'd like the results to count how many days and return
>person | days
>---+---
>jim | 2
>dave | 3
>How would I do this?
Hi Sugapablo,
SELECT person,
COUNT(DISTINCT CONVERT(CHAR(8), date-time, 114)) AS days
FROM YourTable
GROUP BY person
(untested)
Best, Hugo
--
Subscribe to:
Posts (Atom)