sql: easy datetime formatting to varchar

convert(varchar, FIELD_NAME, FORMAT_NUMBER_FROM_CHART)

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

sql: get just the hour from a datetime

SELECT DATEADD(hh, 13, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))

probably a fancier way to keep the hour information, but you can just in an hour variable there to add it back in.

replace getdate() with your date field

SQL: Insert from another table

INSERT INTO Table1 (a, b)     

SELECT c, d     

FROM Table2     

WHERE c = 'something'

SQL 2005: Delete Records Using Another Table

DELETE tobeupdated_table FROM tobeupdated_table  a

WHERE a.tableID = @ID_in

       AND a.otherTableID IN 

           ( SELECT otherTableID  FROM otherTable b

                    WHERE b.someField = @someValue_in

           );

C#: DataTable row conversions.

double value = double.Parse(dr["value"].ToString());

This would parse a value in a datatable field as a double.

Python: Datetime Math

#start code
import datetime
from datetime import datetime as dt
oneDay = datetime.timedelta(days=1)
tomorrow = dt.today()+oneDay
kmlTimeFormat = tomorrow.strftime("%Y-%m-%dT%H:%M:%SZ")
#end code

timedelta resides in the datetime module
today() resides in the datetime.datetime module

then add timedelta (can use months, hours, days, ...) to your datetime.datetime object

I added the kml date string format because thats what i am working on at the moment.


more info

Javascript: Confirmation Alert

if ( confirm("are you sure?") )
alert( 'you chose yes' );
else
alert("you chose no");

SQL: Update a table using another table

UPDATE Table1
SET Table1.fieldToBeUpdated=
(SELECT Table2.fieldToUpdateFrom
FROM Table2
WHERE Table2.commonID= Table1.commonID)
)

WHERE EXISTS (
SELECT Table2.fieldToUpdateFrom
FROM Table2
WHERE Table2.commonID= Table1.commonID)
)

SQL 2005: Unpivot

SELECT recordID, oldColumnNames , newValue
FROM (SELECT recordID, [column1], [column2], [column3] FROM Table) AS p
UNPIVOT (newValue FOR oldColumnNames IN ([column1], [column2], [column3])) AS s

ok this is a little weird bear with me.

its going to take column1, 2, 3 and combine their data into one column. It will create a new column which I've called oldColumnNames and the old column name will be value of the oldColumnNames column.

so

id, column1, column2, column3
1, a, b, c
2, d, e, f

becomes
id, oldColumnNames, newValue
1, column1, a
1, column2, b
1, column3, c
2, column1, d
2, column2, e
2, column3, f

SQL: Insert CSV into Table

BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

stole it from here

JavaScript: Drop Down Menus

In case you ever wondered how to do this here is an A List Apart article on how to do it very simply.

http://www.alistapart.com/articles/dropdowns

Please stop downloading really bad implementations and trying to hack them together. Just read this article (yes all of it) and then do it yourself. I promise you its not that hard.

*Takes your hand* Don't be scared. You'll be better for it I promise.

Javascript: Get a Value from a Drop Down Menu

To get the value:

document.getElementById( 'dropdownmenu_id' ).options[ document.getElementById('dropdownmenu_id').selectedIndex ].value

To get the Option Name:

document.getElementById('dropdownmenu_id').options[ document.getElementById('dropdownmenu_id').selectedIndex ].text

if you use prototype.js framework then you can replace the references to 'document.getElementById' with a $

SQL: Sequence Your Result Set

Say you want to order your result set in a specific way besides just ascending or descending. Here is a simple way to do that.

SELECT your_field,
CASE WHEN your_field = 'c' THEN 0
WHEN your_field = 'a' THEN 1
WHEN your_field = 'b' THEN 2
ELSE 3
END AS order_sequence
FROM your_table
ORDER BY order_sequence

Downside is the query will return an extra field of data (order_sequence)