problem in inserting date in sql server 7 through insert query

hello myself avinash
i am developing on application having vb 6 as front end and sql server 7
as back end.
when i use insert query to insert data in table then the date value of
that query is going as 01/01/1900
my query is as follows

StrSql = "Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
2)
& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
2)
& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
2)
+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
0)
& ")"

and its output is

Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)

in above query though i used cdate to voucherdate value still it save in
database as 01/01/1900 though here it shows right date
plz help me its a very big issue for me & i really just fed of this
problemYou might try running a Profiler trace to capture the actual statement
executed by SQL Server and check for any triggers that might change the
value. Note that SQL Server will interpret an empty string as 1900-01-01.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:9c11bb7aacaeeddcf230738468fd3b72@.localhost.ta lkaboutdatabases.com...
> hello myself avinash
> i am developing on application having vb 6 as front end and sql server 7
> as back end.
> when i use insert query to insert data in table then the date value of
> that query is going as 01/01/1900
> my query is as follows
> StrSql = "Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
> StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
> txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
> "'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
> 2)
> & "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
> val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
> 2)
> & ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
> vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
> vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
> 2)
> + val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
> IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
> 0)
> & ")"
> and its output is
> Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
> Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)
> in above query though i used cdate to voucherdate value still it save in
> database as 01/01/1900 though here it shows right date
> plz help me its a very big issue for me & i really just fed of this
> problem|||hi avinash
i have come across such problems frequently. i would advise u
to set the date format within dtpicker control u are using. Check
properties for the control and set the format to custom. then set the
mask.
thats it.

Regards
Debashishsql

problem in inserting a record whose values are of date and time format.

hello,

I am trying to insert date and time into my table.

insert into <table_name> values('12/12/2006','12:23:04');

but it displays error at " ; "

can anyone help me to figure out the problem

Thanks a lot in advance.

Regards,

Sweety

What happens if you remove ";"?

|||

hi,

thx for responding..i figured out the problem and i solved it..

bye

Sweety

Problem in Datatype Date Convertion

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish

What is the full error message? It should tell you in which component the error is occurring.

-Jamie

sql

Problem in concatinating two paramters to update one column

Using gridview to display the data and sql server 2000

I havea column in the database say departtime of datetime datatype thatcntains the date and time resp(09/19/2007 9:00 PM). I am separating thedate and time parts to display in two different textboxes saytxt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by usingthe convert in sqldatasource. Now i need to update the column in thedatabase and i am using Updatecommand with parameters in aspx lkeupdatecommand = "Update table set departtime = @.departtime" . How cani update my column as datetime by getting the data from 2 texboxes asnow i have 2 textboxes displaying data for single column means if useredit the data in txt1 as(10/19/2007) then on click of update i need topopulate the column daparttime as (10/19/2007 9:00 PM).

Please let me know if you have any questions.

Hello Nick,

What you need is the DateTime.Parse method. See:http://msdn2.microsoft.com/en-us/library/system.datetime.parse(VS.71).aspx

This will create a datetime field of your two text fields.

Jeroen Molenaar.

sql

problem in comparing date

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

Quote:

Originally Posted by cmrhema

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

SQL Server stores dates and time together. Depending on your application and how you are passing your date parameter in as part of your where clause (ie which part of the world you are in) you need to be mindful of your regional settings and look at the CONVERT function in SQL Server

SELECT CONVERT(char(10), GETDATE(), 103)

Will return getdate as character conversion based on the UK regional setting (103)

Regards

Jim :)

Problem in between clause

Hi to all
i have a table which in which date is storing in three separate fields
all datatype char. Values storing are like
01 in day field Mar in month field and 2005 in year fields
. now when i try to get data between two dates results are not coming as
expected.
i m using following query
SELECT a.station_desc
,sum(b.ttl_appl_visited) Total_Token_Booked
,sum(b.ttl_urgent_tokens_today) Urgent_Token
FROM tbl_Value b
left JOIN dbo.Daily_Report_Station ON
dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
where report_day between '15' and '07' and report_month between 'Feb'
and 'Mar'
and report_year between '2006' and '2006'
group by a.station_desc
if i give report day value like report_day between '10' and '20' it
returns me correct value but when first value is greater no result comes
as in the query
Regards,
Farid
*** Sent via Developersdex http://www.examnotes.net ***For a start something like
where report_year = '2006'
and (( report_month = 2 and report_day >= 15 ) or
( report_month = 3 and report_day < 7 ))
If you're stuck with the design you currently have, write a UDF that takes
year, month and day as arguments and returns a datetime, then use
between dbo.MyGetDate( '2006', 'Feb', '15' ) and dbo.MyGetDate( '2006',
'Mar','07')
"Ghulam Farid" wrote:

>
> Hi to all
> i have a table which in which date is storing in three separate fields
> all datatype char. Values storing are like
> 01 in day field Mar in month field and 2005 in year fields
> . now when i try to get data between two dates results are not coming as
> expected.
> i m using following query
> SELECT a.station_desc
> ,sum(b.ttl_appl_visited) Total_Token_Booked
> ,sum(b.ttl_urgent_tokens_today) Urgent_Token
> FROM tbl_Value b
> left JOIN dbo.Daily_Report_Station ON
> dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
> where report_day between '15' and '07' and report_month between 'Feb'
> and 'Mar'
> and report_year between '2006' and '2006'
> group by a.station_desc
>
> if i give report day value like report_day between '10' and '20' it
> returns me correct value but when first value is greater no result comes
> as in the query
> Regards,
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
I'd say the problem is how you use between clause.
Between translates into pair of statements >= and <=
So first part of your condition would be
where report_day >= 15 and report_day <= 7.
This condition returns false, hence all following conditions are not parsed
afaik.
try this example
select 'test' where 2 between 2 and 4
select 'test' where 3 between 4 and 2
Also, do you want data between 07.02.2006 and 15.03.2006 or between 07 and
15 day of Feb and Mar in 2006?
HTH
Peter|||yes u r right first condition is making result false so wht would b the
apropriate condition using same data structure. and its true i want
result between 07-02-2005 and 15-03-2005 but result is not coming. any
help
*** Sent via Developersdex http://www.examnotes.net ***|||You'll have to convert the months to integers. Might be a good use of a
computed column or view with a case report_month when 'Jan' then 1 etc.
Then if you want between 15 Feb and 07 Apr, after converting the months to
integers you would write something like:
where (
( month = 2 and day >= 15) or /* handle February 15-28 */
( month > 2 and month < 4 ) or /* Mar or any other months inbetween */
(month = 4 and day <= 7 ) /* handle final month Apr */
)
"Ghulam Farid" wrote:

> yes u r right first condition is making result false so wht would b the
> apropriate condition using same data structure. and its true i want
> result between 07-02-2005 and 15-03-2005 but result is not coming. any
> help
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Stop doing this and use a DATETIME data type. One of your problems is
that you are mimicking a Cobol record, which has fields for the date
components. If you understood the concept of a column -- which is
nohting like a field -- you would not make this mistake.sql

problem getting right properites

I'm having problems getting the right properties out.
I get the launch date in a column, but that overwrites
with the cube like, [Zone Id].[A].[B].[C]
it inserts the launch date, where the C column should be. In the 2nd query
below, I can get both the date and the value of C, but it gives me double
the rows, and it's in the same column. 'launch date' is a property of [C]
WITH
SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
Id].&[14].&[74].children,
[Zone].CurrentMember.Properties("launch date"))'
SELECT {[Measures].[data1],[Measures].[data2]} ON COLUMNS,
{[ld]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY
ON ROWS
from [All Data]
--
If I use this query, I get double the rows.
WITH SET Top5 AS
' Descendants( [Zone Id].&[14].&[74], 1)'
SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
Id].&[14].&[74].children,
[Zone].CurrentMember.Properties(" launch date"))'
SELECT {[Sends],[Measures].[S Sends]} ON COLUMNS,
{[Top5],[ld]}
ON ROWS
from [All Data]sorry I found it, I just gotta add
DIMENSION PROPERTIES [Zone].[Campaign Launch Date]
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:Odi1UoD%23EHA.2596@.tk2msftngp13.phx.gbl...
> I'm having problems getting the right properties out.
> I get the launch date in a column, but that overwrites
> with the cube like, [Zone Id].[A].[B].[C]
> it inserts the launch date, where the C column should be. In the 2nd
query
> below, I can get both the date and the value of C, but it gives me double
> the rows, and it's in the same column. 'launch date' is a property of [C]
> WITH
> SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
> Id].&[14].&[74].children,
> [Zone].CurrentMember.Properties("launch date"))'
> SELECT {[Measures].[data1],[Measures].[data2]} ON COLUMNS,
> {[ld]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY
> ON ROWS
> from [All Data]
> --
> If I use this query, I get double the rows.
> WITH SET Top5 AS
> ' Descendants( [Zone Id].&[14].&[74], 1)'
> SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
> Id].&[14].&[74].children,
> [Zone].CurrentMember.Properties(" launch date"))'
> SELECT {[Sends],[Measures].[S Sends]} ON COLUMNS,
> {[Top5],[ld]}
> ON ROWS
> from [All Data]
>
>

problem formatting date

Hi all,
I make an insert of date with VB.Net using the "format" function:
format(Now(), "dd/MM/yyyy hh:mm:ss").
In my dev environment it work fine.
But when I put the page on production server
the format fuction change the tima formatting, change ":" with "."
so it pass hh.mm.ss to the DB crashing the insert.
I used a replace function to restore ":", and it work fine.
Bue any idea?
Why this happen
thanks allThis is a pure VB.NET question, so you probably have better luck asking this
in a VB.NET forum.
However, I strongly encourage you to format the datetime as 'YYYYMMDD hh:mm:
ss'. Check
http://www.karaszi.com/SQLServer/info_datetime.asp for the reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henry Cabot Enaus III" <hceiii@.yahoo.com> wrote in message
news:512ff012.0409010324.126ebd78@.posting.google.com...
> Hi all,
> I make an insert of date with VB.Net using the "format" function:
> format(Now(), "dd/MM/yyyy hh:mm:ss").
> In my dev environment it work fine.
> But when I put the page on production server
> the format fuction change the tima formatting, change ":" with "."
> so it pass hh.mm.ss to the DB crashing the insert.
> I used a replace function to restore ":", and it work fine.
> Bue any idea?
> Why this happen
> thanks all

problem formatting date

Hi all,
I make an insert of date with VB.Net using the "format" function:
format(Now(), "dd/MM/yyyy hh:mm:ss").
In my dev environment it work fine.
But when I put the page on production server
the format fuction change the tima formatting, change ":" with "."
so it pass hh.mm.ss to the DB crashing the insert.
I used a replace function to restore ":", and it work fine.
Bue any idea?
Why this happen
thanks all
This is a pure VB.NET question, so you probably have better luck asking this in a VB.NET forum.
However, I strongly encourage you to format the datetime as 'YYYYMMDD hh:mm:ss'. Check
http://www.karaszi.com/SQLServer/info_datetime.asp for the reason.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henry Cabot Enaus III" <hceiii@.yahoo.com> wrote in message
news:512ff012.0409010324.126ebd78@.posting.google.c om...
> Hi all,
> I make an insert of date with VB.Net using the "format" function:
> format(Now(), "dd/MM/yyyy hh:mm:ss").
> In my dev environment it work fine.
> But when I put the page on production server
> the format fuction change the tima formatting, change ":" with "."
> so it pass hh.mm.ss to the DB crashing the insert.
> I used a replace function to restore ":", and it work fine.
> Bue any idea?
> Why this happen
> thanks all
sql

problem formatting date

Hi all,
I make an insert of date with VB.Net using the "format" function:
format(Now(), "dd/MM/yyyy hh:mm:ss").
In my dev environment it work fine.
But when I put the page on production server
the format fuction change the tima formatting, change ":" with "."
so it pass hh.mm.ss to the DB crashing the insert.
I used a replace function to restore ":", and it work fine.
Bue any idea?
Why this happen
thanks allThis is a pure VB.NET question, so you probably have better luck asking this in a VB.NET forum.
However, I strongly encourage you to format the datetime as 'YYYYMMDD hh:mm:ss'. Check
http://www.karaszi.com/SQLServer/info_datetime.asp for the reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henry Cabot Enaus III" <hceiii@.yahoo.com> wrote in message
news:512ff012.0409010324.126ebd78@.posting.google.com...
> Hi all,
> I make an insert of date with VB.Net using the "format" function:
> format(Now(), "dd/MM/yyyy hh:mm:ss").
> In my dev environment it work fine.
> But when I put the page on production server
> the format fuction change the tima formatting, change ":" with "."
> so it pass hh.mm.ss to the DB crashing the insert.
> I used a replace function to restore ":", and it work fine.
> Bue any idea?
> Why this happen
> thanks all

problem creating directory

I have a package that calculates a date, downloads a zip file and then the problems begin.. I WANT to have it create a folder using the same filename (left of the .zip) but my problem appears to be how I'm doing this. It is a calculated variable that has scope for the whole package. I set the source property to false and hard coded the parent directory and then have an expression that concatenates the constant (parent) directory with the global variable and my intent is to use this in place of the source value for the create directory (I then unzip the file into that directory and have a for each loop to process said files and rename them). My problem is that, for some reason, VS calculates the expression and I get an error that says The connection xxxxxx is not found. This error is thrown by the Connections collection when the specific connection element is not found. (xxxxxx is the calculated direcotry and is correct). Why is it doing this calculation and erroring, its job is to create the folder? Thanks in advance.I'm guessing that either you are setting the wrong property, or the package is checking to see if the directory exists before the task to create it has run. You make make sure that DelayValidation is set to TRUE on all appropriate tasks.|||John, you were correct in that it was the wrong property (thank you)! Interestingly enough I also wasn't aware of the delay validation option so both of you responses were very helpful. thanks much!!!

Problem converting to a date...

I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
Try:
select
convert (datetime, '20040301', 112)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
|||Populate a datetime column with the data...
ALTER TABLE YourTable ADD YourDatetimeCol DATETIME
GO
UPDATE YourTable SET YourDateTimeCol=CONVERT(DATETIME, YourNonDateTimeCol)
GO
Optionally, remove the old column, make the new column non-nullable, etc...
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>
|||hi atley,
if the format of datetime string is yyyymmdd then just run following query:
select convert(datetime,'20040301') dt
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi,
Have a look into the below code,
declare @.col1 varchar(20)
declare @.dt datetime
set @.col1 ='20040301'
select @.dt=convert(datetime,@.col1)
select @.dt
Tahnks
Hari
MCDBA
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>