query problem

[rhone0809]

i am new to vb.net...
my problem in this query is that "Format is not a recognized built in name"
this Format() is an sql function right?
is there a header file to call the format() function just like c++?

can anyone help me with this? please!

here's my query code:

command2 = conn.CreateCommand
sqlString = "INSERT INTO checkinout_summary1 ( Userid, CheckTime, [Date], [IN], [Day] ) SELECT checkinout_Extracted1.Userid, checkinout_Extracted1.CheckTime, Format([CheckTime],'mm/dd/yyyy') AS [Date], First(Format([CheckTime],'hh:nn AM/PM')) AS TimeIN, Format([CheckTime],'dddd') AS DDay FROM checkinout_Extracted1 GROUP BY checkinout_Extracted1.Userid, checkinout_Extracted1.CheckTime, Format([CheckTime],'dddd'), checkinout_Extracted1.CheckType HAVING (((checkinout_Extracted1.CheckType)='I')) ORDER BY checkinout_Extracted1.Userid"
command2.CommandText = sqlString
'db2 = New sqlCommand(sqlString, conn)
check = command2.ExecuteNonQuery

[kingsonprisonic]

What you want to do in database....???

I see the query you posted will not get executed. Because you miss some parts of the query...

Like
This is a complete sql query..

Insert Into Table1 (Col1,Col2,Col2) Values(Val1,Val2,Val3)

So i advice you to learn SQL first then try this....

[M.Waqas Aslam]

can you please explain what you want to do , if you have any prob regarding with sql then please post your question in sql forum .
Regards

[rhone0809]

actually there is still another insert query which is executed first.. from table checkinout_extracted1 with this field names ---> [Logid],[Userid],[CheckTime],[CheckType],[Sensorid],[Checked],[Date]
i want the data of the table checkinout_extracted1 to be inserted into checkinout_summary1. the query of this checkinout_summary1 is the first one i have posted...

[pritesh2010]

your codes this part is not working Format() is not sql Command just maniuplate from code to find date and time or see the nblow given link for dateTime
http://www.sql-server-helper.com/tips/date-formats.aspx

Format([CheckTime],'mm/dd/yyyy') AS [Date], 
First(Format([CheckTime],'hh:nn AM/PM')) AS TimeIN, 
Format([CheckTime],'dddd') AS DDay

[Reverend Jim]

You use the CONVERT function. The first parameter is the type you want to convert to (in your case, string), the seconbd parameter is the field to retrieve. The third parameter determines the format. For example

select CONVERT(varchar(50),checktime,101)

Allowable formats are

100 or 0	mon dd yyyy hh:miAM (or PM)
101	        mm/dd/yy
102	        yy.mm.dd
103	        dd/mm/yy
104	        dd.mm.yy
105	        dd-mm-yy
106	        dd mon yy
107	        Mon dd, yy
108	        hh:mm:ss
109 or 9	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	        mm-dd-yy
111	        yy/mm/dd
112	        yymmdd
113 or 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	        hh:mi:ss:mmm(24h)
120 or 20	yyyy-mm-dd hh:mi:ss(24h)
121 or 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	        yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130	        dd mon yyyy hh:mi:ss:mmmAM
131	        dd/mm/yy hh:mi:ss:mmmAM

[rhone0809]

your codes this part is not working Format() is not sql Command just maniuplate from code to find date and time or see the nblow given link for dateTime
http://www.sql-server-helper.com/tips/date-formats.aspx

Format([CheckTime],'mm/dd/yyyy') AS [Date], 
First(Format([CheckTime],'hh:nn AM/PM')) AS TimeIN, 
Format([CheckTime],'dddd') AS DDay

>
@Pritesh2010 >--(:>> thanks.. i got it..
>

[M.Waqas Aslam]

then please mark this thread solved . Regards